Update columns in the same table with different values with postgres

Question

This is a very basic question, but I couldn't find a better way to do this and I have the feeling that I am going about it the wrong way.

Basically I have two values in my table's column that I would like to update to two different values.

So say I have a column name with the value My Name, I would like to change it to Updated My Name, however on the same column, if I also have the value My Name222, I would like to update it to Updated My Name222. Currently I have two Update SQL calls, which look like this:

UPDATE myTable t
SET "name"='My Name' WHERE "name"= 'Updated My Name';
UPDATE myTable t
SET "name"='My Name222' WHERE "name"= 'Updated My Name222';

As I mentioned before, this does work, but I feel there is a better way to do it back to back without having to call UPDATE myTable t twice. How would that be?

Gordon Linoff · Accepted Answer · 2018-08-13 18:46:32Z

6

Personally, I like using values() for this sort of thing:

update my_table
    set name = newname 
    from (values ('My Name', 'Updated My Name'),
                 ('My Name222', 'Updated My Name222')
         ) v(oldname, newname)
    where name = oldname;

This has some advantages over explicit case expressions. First, it is easily generalizable -- just add more rows to v. Second, you don't have to repeat any values, reducing the opportunity to make mistakes.

answered Aug 13, 2018 at 18:46

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

klin · Accepted Answer · 2018-08-13 18:38:55Z

4

There is a way to do this in a single statement using case:

update my_table
set name = case name 
    when 'My Name' then 'Updated My Name'
    else 'Updated My Name222'
    end
where name in ('My Name', 'My Name222');

In this case two separate updates are simpler and quite natural.

However, if you want to modify the column values exactly in the same way in both rows, then this solution makes more sense:

update my_table
set name = concat('Updated ', name)
where name in ('My Name', 'My Name222');

edited Aug 13, 2018 at 18:38

answered Aug 13, 2018 at 17:17

klin

123k15 gold badges240 silver badges262 bronze badges

2 Comments

theJuls Over a year ago

So are the two separate updates the better thing to do in this case?

klin Over a year ago

I think so, mainly because of the legibility of the code. The possible benefits in performance are negligible.

Ramji · Accepted Answer · 2018-08-13 17:15:41Z

1

Another way

    UPDATE tablename
    SET name = CASE WHEN name = 'Updated My Name' THEN 'My Name'
                  WHEN  name = 'Updated My Name222' THEN 'Name222'

             END
where name in ('My Name', 'My Name222');

answered Aug 13, 2018 at 17:15

Ramji

3853 silver badges16 bronze badges

Collectives™ on Stack Overflow

Update columns in the same table with different values with postgres

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related