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.


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)

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.