尝试使用 SQL 从多个表中删除

Trying to delete from multiple tables using SQL

提问by user3662630

I have 4 tables in our application:

我的应用程序中有 4 个表：

• User
• usession
• upklist
• projshare

• 用户
• 用途
• 上行列表
• 项目共享

The last three tables contain a field called session_id.

最后三个表包含一个名为 的字段session_id。

In the code below, the section in parenthesis works to get all session_idvalues from usessiontable for user "awpeople".

在下面的代码中，括号中的部分用于session_id从usession用户“awpeople”的表中获取所有值。

The problem is how do I read this result set into an array and delete from all three tables where session_idis in the array results.

问题是如何将这个结果集读入一个数组并从session_id数组结果中的所有三个表中删除。

Code:

代码：

DELETE FROM usession, 
            upklist, 
            projshar 
WHERE  session_id = (SELECT session_id 
                     FROM   usession 
                     WHERE  delete_session_id IS NULL 
                            AND user_id = (SELECT user_id 
                                           FROM   users 
                                           WHERE  REGEXP_LIKE(USER_NAME, 
                                                  'awpeople', 'i'))); 

回答by Mureinik

deletecan only handle one table at a time, so you'd need three statements:

delete一次只能处理一张表，所以你需要三个语句：

DELETE FROM upklist 
WHERE  session_id = (SELECT session_id 
                     FROM   usession 
                     WHERE  delete_session_id IS NULL 
                            AND user_id = (SELECT user_id 
                                           FROM   users 
                                           WHERE  REGEXP_LIKE(USER_NAME, 
                                                  'awpeople', 'i'))); 

DELETE FROM projshar 
WHERE  session_id = (SELECT session_id 
                     FROM   usession 
                     WHERE  delete_session_id IS NULL 
                            AND user_id = (SELECT user_id 
                                           FROM   users 
                                           WHERE  REGEXP_LIKE(USER_NAME, 
                                                  'awpeople', 'i'))); 

DELETE FROM usession 
WHERE  session_id = (SELECT session_id 
                     FROM   usession 
                     WHERE  delete_session_id IS NULL 
                            AND user_id = (SELECT user_id 
                                           FROM   users 
                                           WHERE  REGEXP_LIKE(USER_NAME, 
                                                  'awpeople', 'i'))); 

Note that since the inner query relies on usersession, you should delete from it last.

请注意，由于内部查询依赖于usersession，您应该最后从中删除。

回答by David Aldridge

If usession has a unique or primary key on session_id, and the other tables have foreign key relationships to it, then you can just delete the row from usession and have the database cascade it to the child tables.

如果使用在 session_id 上具有唯一键或主键，并且其他表与它有外键关系，那么您可以从使用中删除该行并将数据库级联到子表。

回答by diadyne

Multiple-table syntax:

多表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.] [, tbl_name[.]] ... FROM table_references [WHERE where_condition]

删除 [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[. ] [, tbl_name[. ]] ... FROM table_references [WHERE where_condition]

Or:

或者：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name[.] [, tbl_name[.]] ... USING table_references [WHERE where_condition]

从 tbl_name[ 中删除 [LOW_PRIORITY] [QUICK] [IGNORE]。] [, tbl_name[. ]] ... 使用 table_references [WHERE where_condition]

This comes from the oracle DELETE documentation.

这来自oracle DELETE 文档。