MySQL - UPDATE query referring to another table

Question

I need to update some records in one table by clause from another.

table_A - status to change

ID	DID	status	value
1	29	OK	X-9
2	29	OK	X-8
3	29	OK	X-6

table_B - clause

v_1	v_2
X-9	2022-07-13
X-8	2022-06-30
X-6	2022-06-30

I tried by this query but doesnt work...

UPDATE
    table_A
SET
    table_A.status = "NOK"
FROM
    table_A AS table_A
    INNER JOIN table_B AS table_B
        ON table_A.value = table_B.v_1
WHERE
        table_A.DID = 29 
    AND table_A.status = "OK" 
    AND table_B.v_2 < NOW()

I will be grateful for your help

score 1 · Accepted Answer · answered Jul 13 '22 at 10:10

This is basic syntax errors when using UPDATE query Your query should be something like:

UPDATE
    table_A AS table_A
    INNER JOIN table_B AS table_B
        ON table_A.value = table_B.v_1
SET
    table_A.status = "NOK"
WHERE
        table_A.DID = 29 
    AND table_A.status = "OK" 
    AND table_B.v_2 < NOW()

Give it a try

MySQL - UPDATE query referring to another table

1 Answers1