2

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;

1 Answer 1

4

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.)

6
  • 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
    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.
    Feb 18, 2023 at 16:06
  • almost! Leaving out the WHERE clause seems to touch all entries at once. thx
    – vrms
    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.
    Feb 19, 2023 at 13:41
  • yea. that (using a where clause) was what I was looking to avoid
    – vrms
    Feb 19, 2023 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.