Comparing two tables for a UUID change and fix it I have two tables which I'm trying to reconcile the differences of in postgresql.

Table A is old and needs updating.

Table B is an updated, schema identical version of Table A which I have the data for in a temporary table in the database of Table A.

Unfortunately some time after the two databases diverged someone changed the UUIDs of records in table B and I need table A to match table B.

The schema for both tables is:

CREATE TABLE A
(
    uuid VARCHAR(36) NOT NULL,
    template_folder_uuid_parent VARCHAR(36),
    heading VARCHAR(255) NOT NULL,
    image VARCHAR(100),
    downloaded BOOL NOT NULL,
    allow_in TEXT NOT NULL,
    template_group VARCHAR(255) NOT NULL,
    country_filter VARCHAR(1024) NOT NULL,
    user_id INT,
    date_created TIMESTAMP,
    date_modified TIMESTAMP,
    is_modified BOOL NOT NULL,
    image_path VARCHAR(255)
);


I need to search through Table A and Table B and match records based on template_folder_uuid_parent and heading, then set the UUID of the Table A record to the UUID from Table B. Once changed in Table A the UUID will cascade correctly.
UPDATE tbl_a a
SET    uuid = b.uuid
FROM   tbl_b b
WHERE  b.template_folder_uuid_parent = a.template_folder_uuid_parent
AND    b.heading = a.heading
AND    b.uuid &lt;&gt; a.uuid


The last line is to avoid empty updates, which are often forgotten and can be very expensive with big tables.

I would first verify that this update does what you expect it to do before you apply it:

SELECT *
FROM   tbl_a a
JOIN   tbl_b b ON
       b.template_folder_uuid_parent = a.template_folder_uuid_parent
AND    b.heading = a.heading
AND    b.uuid &lt;&gt; a.uuid;