删除多个表中的记录

发布于 2024-09-15 11:21:18 字数 444 浏览 5 评论 0原文

考虑以下几个表：

tab1
-------
userid
email
address
environment

tab2

-------
ecode
company
policy

tab3
-------
id
pan no.
dl no.

这里 tab1 是父表。我想从 tab2 和 tab3 中删除 tab1 的所有 userid。 userid 将位于 tab1 或 tab2 或两者中。这些表是特定于环境的，即 environment/tab1 和 environment/tab2。

如何通过单个查询来完成此操作？

原文

Consider these several tables:

tab1
-------
userid
email
address
environment

tab2

-------
ecode
company
policy

tab3
-------
id
pan no.
dl no.

Here tab1 is the parent table. I want to delete all the userid of tab1 from tab2 and tab3. Either userid will be in tab1 or tab2 or both. These tables are environment specific means environment/tab1 and environment/tab2.

How can this be done with a single query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

在梵高的星空下 2024-09-22 11:21:18

我不是 AS400 的专家，但我不知道有什么方法可以在任何 SQL 方言中做到这一点。您需要启动一个事务，执行单独的删除，然后如果您想确保该操作是原子的（意味着整个操作运行或都不运行），则提交该事务。

代码可能看起来像这样：

BEGIN TRANSACTION

DELETE FROM tab2
WHERE userid IN (SELECT userid FROM tab1)

DELETE FROM tab3
WHERE userid IN (SELECT userid FROM tab1)

COMMIT TRANSACTION

您没有具体指定这些表是如何相关的，所以我进行了猜测。另外，您可能应该进行一些错误处理，以便在遇到错误时发出ROLLBACK。我不知道 AS400（DB2？）的错误处理语法，因此您需要查找它。

I'm not an expert with the AS400, but I don't know of any way to do that in any SQL dialect. You need to start a transaction, perform your separate deletes, then commit the transaction if you want to make sure that it the action is atomic (meaning that the whole thing runs or none of it runs).

The code would probably look something like this:

BEGIN TRANSACTION

DELETE FROM tab2
WHERE userid IN (SELECT userid FROM tab1)

DELETE FROM tab3
WHERE userid IN (SELECT userid FROM tab1)

COMMIT TRANSACTION

You don't specify exactly how those tables are related, so I took a guess. Also, you should probably have some error handling that issues a ROLLBACK if an error is encountered. I don't know the error handling syntax for AS400 (DB2?), so you'll need to look that up.

回复收藏 0 原文