Postgres query to update a column with value of another

The question:

I have a query that is working: But, I’m not sure if it is the best.

update table1
set column1 = column2,
    column2 = 0
where id = 'X';

As you can see, I set the value of column2 to column1, and in the same query, I set column2 to zero.

Is that the best option to do this?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

There is nothing wrong with this update, and it’s actually the preferred way to accomplish this type of update. With this method, you touch the table once instead of twice.

When setting a column to the value of another column, the value of column2 at the beginning of the update is the value applied to column1, even if you’re also updating column2 during the same update.

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment