Sql清理脚本，从一个表中删除不在其他 [英] Sql Cleanup script, delete from one table that's not in the other

问题描述

我们在SQL（DB2）as400中编写了一个清理脚本来清理表。我们正在修复导致数据问题的进程。

SQL： DELETE FROM p6prodpf A WHERE（0 =（SELECT COUNT（* ）FROM P6OPIPF B WHERE B.OPIID = A.OPIID））

它的简单代码检查<$ c $在 P6OPIPF 中没有记录，然后删除 p6prodpf 中的记录的c> p6prodpf / p>

我面临的问题是，有些情况下， p6prodpf 被删除，即使是 P6OPIPF 。

有更好的方法做这个或更安全的方式..有什么理由发生。

脚本在上午凌晨3点运行。

它也有一个排序问题，记录在 P6OPIPF 中，但它在 p6prodpf 中已删除。

解决方案

使用NOT EXISTS而不是0 =：

  DELETE FROM p6prodpf A WHERE NOT EXISTS（SELECT 1 FROM P6OPIPF B WHERE B.OPIID = A.OPIID）
  

We wrote a cleanup script in SQL(DB2) as400 to do cleanup of tables. ps we are fixing the processes that is causing the data issues.

The SQL : DELETE FROM p6prodpf A WHERE (0 = (SELECT COUNT(*) FROM P6OPIPF B WHERE B.OPIID = A.OPIID))

Its simple code to check if theres a record in p6prodpf that has no record in P6OPIPF then delete the record in p6prodpf.

My problem that I am facing is that there's instances where the p6prodpf is being deleted even if theres a record in P6OPIPF.

Is there a better way of doing this or safer way.. Is there any reason why this could be happening.

The script runs 3am in the morning.

It also feels like a sequencing issue but when I check the record in P6OPIPF it exists but its deleted in p6prodpf.

解决方案

Use "NOT EXISTS" instead of "0 =":

DELETE FROM p6prodpf A WHERE NOT EXISTS (SELECT 1 FROM P6OPIPF B WHERE B.OPIID = A.OPIID)

这篇关于Sql清理脚本，从一个表中删除不在其他的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！