Dean Dean - 20 days ago 9
MySQL Question

MYSQL: UPDATE FROM INNER JOIN syntax error

I am trying to update

TableA
with values from
TableB
, matched by the unique
id
field and add a
WHERE
condition.
Searched the web and found the code below. For some reason MYSQL states that there's as syntax error.

UPDATE
TableA
SET
TableA.field1 = TableB.field1
FROM
TableA
INNER JOIN
TableB
ON
TableA.id = TableB.id
WHERE
TableA.field1 <> ''
LIMIT 100;

Answer

The correct syntax in MySQL is:

UPDATE TableA INNER JOIN
       TableB
       ON TableA.id = TableB.id
    SET TableA.field1 = TableB.field1
WHERE TableA.field1 <> '';

As a note: you cannot use LIMIT with a JOIN.

If you want to use LIMIT, you can do:

UPDATE TableA 
    SET TableA.field1 = (SELECT TableB.field1 FROM TableB WHERE TableA.id = TableB.id)
    WHERE TableA.field1 <> '' AND
          EXISTS (SELECT 1 FROM TableB WHERE TableA.id = TableB.id)
    LIMIT 100;

(You can leave out the EXISTS expression if you know there is always a match in TableB. You can add LIMIT 1 to the subquery if more than one match is possible.)

I would advise you to always use ORDER BY with LIMIT as well. That way, you can control which rows are being updated.