How do i perform a conditional update in SQL with data from multiple tables?

Question

Trying to run an update on a column and i'm getting a syntax error on the FROM line connecting the tables

UPDATE inv_loc
SET inv_loc.product_group_id = 'TEMP'
WHERE inv_mast_ud.eh_spk LIKE '%T'

FROM
inv_mast_ud
left join inv_loc on inv_mast_ud.inv_mast_uid = inv_loc.inv_mast_uid

score 3 · Accepted Answer · answered Dec 02 '19 at 16:38

WHERE comes after FROM. I think you want:

UPDATE inv_loc
    SET inv_loc.product_group_id = 'TEMP'
    FROM inv_loc JOIN
         inv_mast_ud
         ON inv_mast_ud.inv_mast_uid = inv_loc.inv_mast_uid
    WHERE inv_mast_ud.eh_spk LIKE '%T';

Note that I changed the LEFT JOIN to an INNER JOIN. You are updating inv_loc, so it makes no sense that that table is the second table in a LEFT JOIN.

I assume you actually want to filter the rows, so a LEFT JOIN is not needed. Otherwise, you would not need inv_mast_ud.

score 2 · Answer 2 · answered Dec 02 '19 at 16:37

The WHERE clause belongs at the end of the update join statement:

UPDATE il
SET il.product_group_id = 'TEMP'
FROM inv_loc il
INNER JOIN inv_mast_ud imu
    ON imu.inv_mast_uid = il.inv_mast_uid
WHERE
    imu.eh_spk LIKE '%T';

See SQL update query using joins for a good canonical answer to your question.

How do i perform a conditional update in SQL with data from multiple tables?

2 Answers2