Postgres query to update a column with value of another

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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

Leave a Reply