Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted
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 <> 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 <> a.uuid;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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