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?
Best Answer