UPDATE SET = SELECT FROM

Question

I am trying to update table based on a select query using this:

UPDATE branches SET name = 
(SELECT CONCAT(comp.name," ",bra.subsurb) as newname 
FROM companies comp 
RIGHT JOIN branches bra 
ON comp.id = bra.company_id)

Which, according to this question, should work but this produces an error: You can't specify target table 'branches' for update in FROM clause

Not sure what I am doing wrong.

EDIT:

Eventually this query did what I'm after:

UPDATE branches bra LEFT JOIN companies comp ON comp.id = bra.company_id SET bra.name = CONCAT(comp.name," ",bra.subsurb)

Great minds think alike, you found the answer right while I posted it. ;P — AdamMc331, Jun 23 '15 at 13:49

score 5 · Accepted Answer · answered Jun 23 '15 at 13:48

5

The error itself is pretty self explanatory, you are selecting from the table you are updating. Instead, you can use JOIN in the update statement. Try this:

UPDATE branches b
LEFT JOIN companies c ON c.id = b.company_id
SET b.name = CONCAT(c.name, " ", b.subsurb);

Note that because you're using a left join here to select all branches, regardless of whether or not they have a company, you may get null values for some names, so be careful about that.

answered Jun 23 '15 at 13:48

AdamMc331

16,492
10
71
133

Just tried this query before you write your answer and it worked. Thanks :) – Naguib Ihab Jun 23 '15 at 13:49

score 2 · Answer 2 · edited May 23 '17 at 11:44

In MySQL, you can't modify the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html

You will need to stop using the nested subquery and execute the operation in two parts, or alternatively use a simple where clause.

For more information please go through the following link

MySQL Error 1093 - Can't specify target table for update in FROM clause

score 0 · Answer 3 · answered Jun 23 '15 at 13:54

The error you are getting is a restriction in MySQL.

To approach this problem, first write a SELECT statement that returns both the existing column value, and the new value you want to assign to the column:

e.g.

  SELECT b.company_id
       , b.name                       AS old_name
       , CONCAT(c.name,' ',b.subsurb) AS new_name
    FROM branches b
    LEFT
    JOIN companies c
      ON c.id = b.company_id

NOTE: beware of NULL values returned for c.name (possible because of the outer join). CONCAT including a NULL may return NULL.

Once that is returning the rows you want to update, convert that to an UPDATE by replacing the SELECT ... FROM with the keyword UPDATE, and add a SET clause immediately before the WHERE clause:

  UPDATE branches b
    LEFT
    JOIN companies c
      ON c.id = b.company_id
     SET b.name = CONCAT(c.name,' ',b.subsurb)

UPDATE SET = SELECT FROM

3 Answers3