Mysql – What’s faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE

dmlMySQLperformance

What's best performance wise, two separate UPDATEs:

UPDATE table1
    SET enabled = 0
    WHERE code NOT IN (
        SELECT id
        FROM table2
    );

UPDATE table1
    SET enabled = 1
    WHERE code IN (
        SELECT id
        FROM table2
    );

Or a single UPDATE with a CASE?

UPDATE table1
    SET enabled = (
        CASE
            WHEN code IN (SELECT id FROM table2) THEN 1 
            ELSE 0 
        END
    );

(and let's consider there are no NULL values in the relevant columns).

What would perform best, supposed code and id columns have are indexed?

I can't really tell because I don't have significant tables, and the only difference EXPLAIN gives is that the former version uses the WHERE (but I analyzed only one UPDATE) – I'd say that a plus, but I'm unsure if that's enough.

I'm working with MySQL.

Best Answer

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL

UPDATE table1 A
LEFT JOIN table2 B
ON A.code = B.id
SET A.enabled = 1 - ISNULL(B.id);

Why should this work ?

  • If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2
  • If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) because it is not in table2

@ypercube suggested 

UPDATE table1 A
LEFT JOIN table2 B
ON A.code = B.id
SET A.enabled = (B.id IS NOT NULL);

GIVE IT A TRY !!!

Related Question