NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
ORDER BY
"public".qa.qa_localid ASC
</pre>
=== UPDATE Location rows===__NOEDITSECTION__
It are 5 fields that needs to be updated for rows that should be transformed into NEW_LOCATION rows
# approval_state, should be NEW_LOCATION
# to_guid, should be same as from_guid
# system_link, should be NULL
# to_link_is_version, should be '''t'''
# update_type, should be NULL
This query identifies which rows to update. I put them in a temporary table ''new_link_loc''.
<pre>
SELECT
"public"."location".location_guid,
"public"."location".location_localid,
Min("public".link.data_entry_date)
FROM
"public"."location"
INNER JOIN "public".locationinfoversion ON "public".locationinfoversion.location_guid = "public"."location".location_guid
INNER JOIN "public".link ON "public".locationinfoversion.locationinfoversion_guid = "public".link.from_guid
WHERE
not exists ( select 1 from locationinfoversion
where location.location_guid = locationinfoversion.location_guid
and exists ( select 1 from link where approval_state = 'NEW_LOCATION' and link.from_guid = locationinfoversion.locationinfoversion_guid ) ) AND
"public".locationinfoversion.link_only = 'f'
GROUP BY
"public"."location".location_guid,
"public"."location".location_localid
ORDER BY
2 ASC
</pre>
<pre>
UPDATE link
SET
approval_state = 'NEW_LOCATION',
to_guid = from_guid,
system_link = NULL,
to_link_is_version = 't',
update_type = NULL
FROM
"public".locationinfoversion
INNER JOIN "public".new_link_loc ON "public".new_link_loc.location_guid = "public".locationinfoversion.location_guid
INNER JOIN "public".gazetteer ON "public".locationinfoversion.gazetteer_guid = "public".gazetteer.gazetteer_guid
WHERE
"public".locationinfoversion.locationinfoversion_guid = "public".link.from_guid AND
"public".locationinfoversion.link_only = 'f' AND "public".new_link_loc."min" = "public".link.data_entry_date
</pre>
