SQL删除行只有一组列的所有值都等于
问题描述:
考虑两个表SQL删除行只有一组列的所有值都等于
表一:
Fields :
- a_ID1
- a_Status
- a_Key
表B:
Fields :
- b_ID
- b_Status
- FK_a_key
有没有办法删除来自两个表的一组行关于以下条件:
删除每一行,其中FK_a_key = xxx and a_key = xxx
但仅当非这行的e的状态字段与“2”不同?
答
一对夫妇的not exists
调用应该做的伎俩:
DELETE FROM a
WHERE a_key = 'xxx' AND
NOT EXISTS (SELECT *
FROM a
WHERE a_key = 'xxx' AND a_status != 2) AND
NOT EXISTS (SELECT *
FROM b
WHERE FK_a_key = 'xxx' AND b_status != 2)
DELETE FROM b
WHERE b_key = 'xxx' AND
-- Frankly, both these conditions are are redundant -
-- they've been checked in the previous statement
NOT EXISTS (SELECT *
FROM a
WHERE a_key = 'xxx' AND a_status != 2) AND
NOT EXISTS (SELECT *
FROM b
WHERE FK_a_key = 'xxx' AND b_status != 2)