postgres - copy values from one column to another column (same table)

Question

I want to copy the entire content from one column to another (same datatype) and thought of a subselect to be part of the equasion.

UPDATE tab01 
  SET column2=(SELECT column1 FROM tab01);

ERROR:  more than one row returned by a subquery used as an expression

that error makes completely sense as the outpout of my subselect has many rows.

Is there a way to copy all values into the other coloumn, without creating any sort of loop around something like ...

UPDATE tab01 
  SET column2=(SELECT column1 FROM tab01 WHERE id=1)
  WHERE ID=1;

J.D. · Accepted Answer · 2023-02-19 16:56:12Z

6

Why not just this?

UPDATE tab01 
SET column2 = column1

I believe the above should work. No need for a subquery, the field should be available of the same row which is being updated.

(Per your comments, you did not want to filter which rows get updated, so I've removed the WHERE clause.)

answered Feb 18, 2023 at 13:31

J.D.

39.5k12 gold badges60 silver badges134 bronze badges

timestamp in my example is the name of a column (which also has the datatype timestamp). That's pobably confusing, sorry. I have updated my question, to make this more clear.
– vrms
Commented Feb 18, 2023 at 16:01
@vrms Yes I understand that. You're allowed to reference other columns you want to use on the right hand side of the equals sign of an update statement.
– J.D.
Commented Feb 18, 2023 at 16:06
almost! Leaving out the WHERE clause seems to touch all entries at once. thx
– vrms
Commented Feb 19, 2023 at 13:07
@vrms Gotcha, np. I was only matching your second example which also has a WHERE clause on which rows were being updated. But yes, either way, you can directly access other columns of the same row being updated.
– J.D.
Commented Feb 19, 2023 at 13:41
yea. that (using a where clause) was what I was looking to avoid
– vrms
Commented Feb 19, 2023 at 16:35

| Show 1 more comment

1 Answer 1