PostgreSQL update from subquery

Question

I have a following PostgreSQL table:

CREATE TABLE users (
    user_id INTEGER DEFAULT nextval('users_user_id_seq') NOT NULL,
    user_old_id CHARACTER VARYING(36),
    created_by INTEGER,
    created_by_old character varying(36),
    last_updated_by INTEGER,
    last_updated_by_old character varying(36),
    CONSTRAINT users_pkey PRIMARY KEY (user_id)
);

Based on data in this table I need to update:

created_by field with user_id from this table for every row where created_by_old = user_old_id Please note that created_by_old can be NULL and therefore must be avoided in this case.
last_updated_by field with user_id from this table for every row where last_updated_by_old = user_old_id Please note that last_updated_by_old can be NULL and therefore must be avoided in this case.

This is a sample data:

Actual:

user_id | user_old_id | created_by | created_by_old | last_updated_by | last_updated_by_old 
--------------------------------------------------------------------------------------------
1       | aaa         |            | ccc            |                 | bbb
--------------------------------------------------------------------------------------------
2       | bbb         |            | ddd            |                 | aaa
--------------------------------------------------------------------------------------------
3       | ccc         |            |                |                 | ddd
--------------------------------------------------------------------------------------------
4       | ddd         |            | aaa            |                 |

Expected:

user_id | user_old_id | created_by | created_by_old | last_updated_by | last_updated_by_old 
--------------------------------------------------------------------------------------------
1       | aaa         |  3         | ccc            |   2             | bbb
--------------------------------------------------------------------------------------------
2       | bbb         |  4         | ddd            |   1             | aaa
--------------------------------------------------------------------------------------------
3       | ccc         |            |                |   4             | ddd
--------------------------------------------------------------------------------------------
4       | ddd         |  1         | aaa            |                 |

I think it can be implemented with a subquery but can't figure out by myself right now how to implement this query. Please help.

@sagi I have updated my question with a sample data and expected result — alexanoid
– alexanoid, Commented Nov 3, 2016 at 10:56

user330315 · Accepted Answer · 2016-11-03 13:26:33Z

8

If you want a more efficient update statement you can use a derived table that gets the information you want as the source of your update statement:

update users u1
  set created_by = t.new_created_by, 
      last_updated_by = t.new_updated_by
from ( 
  select u2.user_id, u2.user_old_id,
         cr.user_id as new_created_by, 
         lu.user_id as new_updated_by
  from users u2
    left join users cr on cr.user_old_id = u2.created_by_old
    left join users lu on lu.user_old_id = u2.last_updated_by_old
) t
where t.user_id = u1.user_id;

Usually the target table of the update should never be repeated in the from clause, but this case is a rare example where it can't be avoided.

But this will only work properly if user_old_id is also unique, not only user_id.

Online example: http://rextester.com/HKM21985

answered Nov 3, 2016 at 13:26

user330315

Sign up to request clarification or add additional context in comments.

Comments

sagi · Accepted Answer · 2016-11-03 10:59:38Z

4

Not too efficient ,but if that's not an issue you can do this :

UPDATE users u
SET u.created_by = (SELECT t.user_id FROM users t
                    WHERE u.created_by_old = t.user_old_id),
    u.last_updated_by = (SELECT s.user_id FROM users s
                         WHERE u.last_updated_by = s.user_old_id)

answered Nov 3, 2016 at 10:59

sagi

40.6k7 gold badges66 silver badges87 bronze badges

4 Comments

alexanoid Over a year ago

Thanks. Please note that created_by_old or last_updated_by_old in some rows can be empty and therefore update of appropriate field must be avoided in this case. How to update this query in order to support this case ?

sagi Over a year ago

Why? If it's NULL then the result will be NULL as well . @alexanoid

alexanoid Over a year ago

Thanks. Also I tried this query

UPDATE users u SET u.created_by = (SELECT t.user_id FROM users t WHERE u.created_by_old = t.user_old_id), u.last_updated_by = (SELECT s.user_id FROM users s WHERE u.last_updated_by_old = s.user_old_id)

but it fails with a following error:

ERROR:  column "u" of relation "users" does not exist LINE 1: UPDATE users u SET u.created_by = (SELECT t.user_id FROM use...

alexanoid Over a year ago

I have updated u.created_by to simple created_by and now everything works fine! Thank you !

Collectives™ on Stack Overflow

PostgreSQL update from subquery

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related