FewFlyBy FewFlyBy - 1 month ago 7
SQL Question

Update field set to value of row in same field

I'm trying to update a specific field and set it equal to value of a row in the same field.

What have I tried so far is this:

enter image description here

mysql> UPDATE tblitem SET imagefilename = (SELECT imagefilename from tblitem where itemid=2) where itemid=1'
1093 - You can't specify target table 'tblitem' for update in from clause


What I am trying to do here is to update the value of itemid 1 to the value of itemid 2.

Is that even possible? Thank you in advance.

Answer

Use a join instead:

UPDATE tblitem t JOIN
       (SELECT imagefilename from tblitem where itemid = 2
       ) t2
    SET t.imagefilename = t2.imagefilename
    WHERE itemid = 1;

The SQL standard and other databases allow you to refer to the table being updated elsewhere in the update statement. However, MySQL does not allow this. The JOIN is a simple enough work-around.

Comments