2
votes

Mettre à jour pour basculer une colonne booléenne dans une seule instruction

Comment puis-je combiner dans une seule instruction sql les deux requêtes de la manière la plus efficace?

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

Et pareil mais activer le drapeau pour les ids restants :

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

postgresql performance sql query-optimization

1 commentaires

Votre deuxième requête ne change pas la valeur, elle la définit simplement sur true.

4 Réponses :

2
votes

Eh bien, vous pouvez utiliser une expression CASE :

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

Dans Postgres, vous simplifieriez cela en:

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

0 commentaires

0
votes

Vous pouvez utiliser une jointure sur les deux tables, puis une instruction case pour déterminer s'il y a une valeur dans le TABLEAU 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

0 commentaires

2
votes

Dans Postgres, vous pouvez utiliser LEFT JOIN dans une instruction UPDATE avec la syntaxe suivante:

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

0 commentaires

2
votes

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:
Find records where join doesn't exist
Select rows which are not present in other table
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.

0 commentaires