CODEWITHSUNDEEP
mysqlsqljoinsql-updateinner-join
JOAO12
JOAO12

Reputation: 55

Update different columns using inner join

Is it possible to put these 2 codes in just one line of code? What needs to change?

UPDATE table1 AS t1 INNER JOIN table2 AS t2 ON t1.id = t2.id SET t1.column2 = 1;
UPDATE table1 AS t1 INNER JOIN table3 AS t3 ON t1.id = t3.id SET t1.column3 = 1;

Thanks.

Upvotes: 1

Views: 59

Answers (1)

GMB
GMB

Reputation: 222462

Yes that's possible, but it's a bit cumbersome. Basically that's two LEFT JOINS and some conditional logic:

UPDATE table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
LEFT JOIN table3 t2 ON t3.id = t1.id
SET 
    t1.column2 = CASE WHEN t2.id IS NOT NULL THEN 1 ELSE t1.column2 END,
    t1.column3 = CASE WHEN t3.id IS NOT NULL THEN 1 ELSE t1.column3 END
WHERE t2.id IS NOT NULL OR t3.id IS NOT NULL

You could also use EXISTS and correlated subqueries:

UPDATE table1 t1
SET 
    t1.column2 = CASE
        WHEN EXISTS(SELECT 1 FROM table2 t2 WHERE t2.id = t1.id)
        THEN 1
        ELSE t1.column2
    END,
    t1.column3 = CASE
        WHEN EXISTS(SELECT 1 FROM table3 t3 WHERE t3.id = t1.id)
        THEN 1
        ELSE t1.column3
    END
WHERE 
    EXISTS(SELECT 1 FROM table2 t2 WHERE t2.id = t1.id)
    OR EXISTS(SELECT 1 FROM table3 t3 WHERE t3.id = t1.id)

Upvotes: 2

Related Questions