CODEWITHSUNDEEP
mysqlsqljoin
Dean
Dean

Reputation: 8065

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;

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

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.

Upvotes: 1

Related Questions