Syntactic error while laying down an UPDATE statement with a JOIN operation-CodePudding

I am updating a table based on a value in another table but I cannot figure out why it is throwing an error at the FROM clause:

UPDATE sn
SET sn.delay = 'Yes'
FROM arh ar
INNER JOIN tbl_salesactivity_new sn ON sn.shipment = ar.shipment_id
WHERE ar.ardelay > '0';

CodePudding user response：

In MySQL the correct syntax to update a table using a JOIN operation is following:

UPDATE     tbl_salesactivity_new sn
INNER JOIN arh ar
        ON sn.shipment = ar.shipment_i
       AND ar.ardelay > '0'
SET sn.delay = 'Yes'

To learn more about this syntax, check the examples found at this link.

EDIT. Adding another option >> moving the condition in the WHERE clause (ysth's suggestion)

UPDATE     tbl_salesactivity_new sn
INNER JOIN arh ar
        ON sn.shipment = ar.shipment_i
SET sn.delay = 'Yes'
WHERE ar.ardelay > '0'