2

I have a large table that includes rows like the following:

Query:

select * 
from cartographic_text_vm 
where fid = 'osgb5000005104723440' 
order by ckpt;

Results:

-[ RECORD 1 ]---------------+---------------------------------------------------------
change_history              | 2012-09-05,New
descriptive_group           | Buildings Or Structure
descriptive_term            |
feature_code                | 10026
fid                         | osgb5000005104723440
cartographic_text_version   | 1
make                        | Manmade
physical_level              | 50
physical_presence           |
text_render_anchor_position | 4
text_render_font            | 1
text_render_height          | 2.125
text_render_orientation     | 0
text_string                 | (Secondary)
theme                       | Buildings
version_date                | 2012-09-06 00:00:00
primary_key                 | 8240079
partition_key               | 2
random_number               | 9
geom                        | 0101000020346C000085EB51B8486B1B417B14AE47E9AFFB40
ckpt                        | 1
end_ckpts                   | {7}
transact_status             |

-[ RECORD 2 ]---------------+---------------------------------------------------------
change_history              | 2012-09-05,New,2015-10-25,TextChange
descriptive_group           | Buildings Or Structure
descriptive_term            |
feature_code                | 10026
fid                         | osgb5000005104723440
cartographic_text_version   | 2
make                        | Manmade
physical_level              | 50
physical_presence           |
text_render_anchor_position | 4
text_render_font            | 1
text_render_height          | 2.125
text_render_orientation     | 0
text_string                 | (secondary)
theme                       | Buildings
version_date                | 2015-10-26 00:00:00
primary_key                 | 8240079
partition_key               | 2
random_number               | 124
geom                        | 0101000020346C000085EB51B8486B1B417B14AE47E9AFFB40
ckpt                        | 7
end_ckpts                   | {61}
transact_status             |

-[ RECORD 3 ]---------------+---------------------------------------------------------
change_history              | 2012-09-05,New,2015-10-25,TextChange,2016-04-04,Modified
descriptive_group           | Buildings Or Structure
descriptive_term            |
feature_code                | 10026
fid                         | osgb5000005104723440
cartographic_text_version   | 3
make                        | Manmade
physical_level              | 50
physical_presence           |
text_render_anchor_position | 4
text_render_font            | 1
text_render_height          | 2.125
text_render_orientation     | 0
text_string                 | (secondary)
theme                       | Buildings
version_date                | 2016-04-05 00:00:00
primary_key                 | 123465 <- needs to be changed to 8240079
partition_key               | 2
random_number               | 135
geom                        | 0101000020346C0000AE47E17A446B1B41C3F5285CE7B0FB40
ckpt                        | 61
end_ckpts                   |
transact_status             |

I want to search for rows that have the same value for fid, but different values for primary_key. The database has a versioning extension enabled, so it's important that each version of a fid has the same primary_key.

When you look at the three records above, they are all in different checkpoints as denoted by the value in the ckpt field. The lowest number is the original version of the record, all of the records that have a higher ckpt value are modifications of that original record. I want to update any records that have a different primary_key from the original record to that value. As you can see in Record 3, the primary_key is 123456 which needs to be updated to the primary_key in record 1.

Any pointers in how the SQL should look?

2
  • For anyone searching for this in the future, the SQL query I ended up using was: update cartographic_text_vm set primary_key=new.primary_key from (select * from cartographic_text_vm where (fid, ckpt) in (select fid, min(ckpt) from cartographic_text_vm group by fid)) new where cartographic_text_vm.fid=new.fid and cartographic_text_vm.primary_key != new.primary_key; May 10 '16 at 15:41
  • 2
    Please consider posting that as an answer (and accepting it later if no-one comes up with a better suggestion) instead of a comment. That's a common practice here, even when answering own questions.
    – Andriy M
    May 10 '16 at 17:34
0
update cartographic_text_vm 
set primary_key = new.primary_key 
from 
    (select *  
     from cartographic_text_vm 
     where (fid, ckpt) in 
         (select fid, min(ckpt) 
          from cartographic_text_vm 
          group by fid)
    ) new 
where cartographic_text_vm.fid = new.fid 
  and cartographic_text_vm.primary_key != new.primary_key;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.