Update to toggle a boolean column in a single statement

Question

How can I combine in a single sql statement both queries in the most effective way?

UPDATE TABLE_A a
SET a.is_active = FALSE
WHERE a.id IN (SELECT id FROM TABLE_B);

And the same but toggle the flag for the remaining ids:

UPDATE TABLE_A a
SET a.is_active = TRUE
WHERE a.id NOT IN (SELECT id FROM TABLE_B);

Your second query doesn't toggle the value, it just sets it to true. — Nick, Mar 22 '19 at 01:12

Gordon Linoff · Answer 1 · 2019-03-22T02:48:39.093

4

Well, you can do use a CASE expression:

UPDATE TABLE_A a
    SET a.is_active = (CASE WHEN a.id IN (SELECT id FROM TABLE_B)
                            THEN FALSE ELSE TRUE
                       END);

In Postgres, you would simplify this to:

UPDATE TABLE_A
    SET is_active = (id IN (SELECT b.id FROM TABLE_B b));

edited Mar 22 '19 at 02:48

answered Mar 22 '19 at 01:10

Gordon Linoff

1,242,037
58
646
786

score 3 · Answer 2 · answered Mar 22 '19 at 01:17

3

In Postgres, you can use LEFT JOIN within an UPDATE statement with the following syntax:

UPDATE a 
SET a.is_active = CASE WHEN b.id IS NULL THEN FALSE ELSE TRUE
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.id = b.id

answered Mar 22 '19 at 01:17

GMB

216,147
25
84
135

Erwin Brandstetter · Answer 3 · 2019-03-22T03:47:43.097

UPDATE table_a a
SET    is_active = NOT EXISTS (SELECT FROM table_b b WHERE b.id = a.id);

That's assuming both id columns are NOT NULL.

Else, rows with table_a.id IS NULL are not updated at all in your original, because NULL is neither IN nor NOT IN any set.

And if there are any NULL values in table_b.id none of your rows end up with a.is_active = TRUE, since a.id NOT IN (SELECT id FROM TABLE_B) is either FALSE or NULL but never TRUE in that case. NOT IN is infamous for this kind of "trap". Related:

This query with EXISTS updates all rows. table_a.id IS NULL results in is_active = TRUE, just like for other IDs that are not found in table_b, and NULL values in table_b.id make no difference whatsoever.

EXISTS is also attractive in particular if there can be (many) duplicate values in table_b.id - better performance. table_b.id should be indexed in any case.

score 0 · Answer 4 · answered Mar 22 '19 at 01:14

You can use a join on the two tables, then a case statement to determine if there is a value in TABLE B:

CREATE TABLE #TABLE_A (Id int, is_active bit)
CREATE TABLE #TABLE_B (Id int)


INSERT INTO #Table_A 
    VALUES   (1, NULL)
            ,(2, NULL)
            ,(3, NULL)
            ,(4, NULL)

INSERT INTO #TABLE_B 
    VALUES (1),(3)


SELECT * FROM #TABLE_A

UPDATE a
    SET is_active = (CASE WHEN b.id IS NULL THEN 1 ELSE 0 END)
FROM    #TABLE_A a
            LEFT OUTER JOIN #TABLE_B b
                ON      a.id = b.Id

SELECT * FROM #TABLE_A

Update to toggle a boolean column in a single statement

4 Answers4