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.