MYSQL: UPDATE FROM INNER JOIN syntax error

Question

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;

Gordon Linoff · Accepted Answer · 2016-11-20 17:54:17Z

1

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.

answered Nov 20, 2016 at 17:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL: UPDATE FROM INNER JOIN syntax error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related