Does the following query count as an MySQL update statement with a left outer join where I'm left joining table B to table A on state and id? Or do I have to specify "left outer join B on"?
Is this correct?
UPDATE A
SET A.CITY = B.CITY
FROM B WHERE A.STATE = B.STATE
AND A.ID = B.ID;
Or does it have to be like this?
UPDATE A
SET A.CITY = B.CITY
FROM A LEFT OUTER JOIN B ON A.STATE = B.STATE
AND A.ID = B.ID;
MySQL does support multi-table UPDATE syntax. This is not standard ANSI/ISO SQL, but MySQL implements it as an extension to the standard (vendors do this frequently).
But the syntax you show doesn't match the syntax supported by MySQL. Read the following reference documentation to see the syntax:
The following is an inner join, which will only update rows in A when there is a matching row in B.
UPDATE A JOIN B
SET A.CITY = B.CITY
WHERE A.STATE = B.STATE AND A.ID = B.ID;
You can also write the same inner join this way:
UPDATE A JOIN B
ON A.STATE = B.STATE AND A.ID = B.ID
SET A.CITY = B.CITY;
The following is a left outer join, which will update all rows in A, even if there is no matching row in B. In those cases, it will set A.CITY = NULL.
UPDATE A LEFT OUTER JOIN B
ON A.STATE = B.STATE AND A.ID = B.ID;
SET A.CITY = B.CITY;