Update a column in all rows in db with second part of a column within the same row-CodePudding

I'm trying to update all rows in my db to break apart name (last, first) to have last_name, first_name and display_name but I am not sure where to get the id of the current row being updated. My current sql script is something along the lines of:

UPDATE user_profile 
SET last_name=(SELECT split_part(display_name,',',1) 
               FROM user_profile WHERE id=<current_id>;) 
WHERE last_name IS Null;

CodePudding user response：

You don't need to specify that current row id. A plain update operates in a per-row context. If you use a reference to display_name, it'll assume you mean the one in the current row.

UPDATE user_profile 
SET   last_name = split_part(display_name,',',1)
WHERE last_name IS null;

You can also split into the two fields in one go:

UPDATE user_profile 
SET (first_name, last_name)
  = (coalesce(first_name,
              split_part(display_name,',',2)),
     coalesce(last_name,
              split_part(display_name,',',1)) );

coalesce() returns the first non-null value in its argument list, so if the field is already populated, it won't change it.

Online demo

If you really wanted to, you could alias both instances of the table and match them, like you would with any other table

UPDATE user_profile AS u1
SET last_name=(SELECT split_part(u2.display_name,',',1)
               FROM user_profile AS u2 WHERE u2.id=u1.id) 
WHERE last_name IS null;

and you don't necessarily need the subselect for that

UPDATE user_profile AS u1
SET last_name=split_part(u2.display_name,',',1)
FROM user_profile AS u2 
WHERE u2.id=u1.id
AND u1.last_name IS null;

That's handy when you want to specifically reference some other row in the same table.