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)