我从一个表中删除，如果其中一列为空，我需要使用另一列

Question

DELETE FROM HERAPERM.SCRUBLITST
WHERE
CASE 
WHEN ODUDAT IS NOT NULL
THEN ODUDAT > to_char(current_date - 2 month, 'MM')   
ELSE ODCDAT > to_char(current_date - 2 month, 'MM')         
END

Answer 1

这里有一种方法可以获得您想要的结果。创建一个临时表来保存要删除的行的主键，然后在收集完所有行后将其从表中删除。

DECLARE @foundIDs TABLE ( ID INT )
INSERT INTO @foundIDs
SELECT ID FROM HERAPERM.SCRUBLITST
WHERE ODUDAT IS NULL
AND ODUDAT > to_char(current_date - 2 month, 'MM')
INSERT INTO @foundIDs
SELECT ID FROM HERAPERM.SCRUBLITST
WHERE ODUDAT IS NOT NULL
AND ODCDAT > to_char(current_date - 2 month, 'MM')
DELETE FROM HERAPERM.SCRUBLITST
WHERE ID IN (SELECT ID FROM @foundIDs)

在评论了不能在DB2中声明临时表之后，这里有一种替代方法：

DELETE FROM HERAPERM.SCRUBLITST
WHERE ID IN (SELECT ID FROM HERAPERM.SCRUBLITST
WHERE ODUDAT IS NULL
AND ODUDAT > to_char(current_date - 2 month, 'MM'))
OR ID IN (SELECT ID FROM HERAPERM.SCRUBLITST
WHERE ODUDAT IS NOT NULL
AND ODCDAT > to_char(current_date - 2 month, 'MM'))

Answer 2

更简单的解决方案，无需相关的子查询和ID:

DELETE FROM HERAPERM.SCRUBLITST
WHERE COALESCE (ODUDAT, ODCDAT) > to_char(current_date - 2 month, 'MM');