CODEWITHSUNDEEP
mysqlsql
wobsoriano
wobsoriano

Reputation: 13462

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.

Upvotes: 0

Views: 70

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

In MySQL, if you're doing an UPDATE/INSERT/DELETE queries on a table, you can't reference the said table in the inner query. One workaround is to use a subquery inside the inner query:

UPDATE tblitem 
SET imagefilename = 
(
    SELECT imagefilename 
    FROM (SELECT * FROM tblitem) AS t 
    WHERE itemid = 2
)
WHERE itemid = 1

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 1

Related Questions