尝试使用 SQL 从多个表中删除 [英] Trying to delete from multiple tables using SQL

问题描述

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

I have 4 tables in our application:

• 用户
• 使用
• upklist
• 项目共享

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

The last three tables contain a field called session_id.

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

In the code below, the section in parenthesis works to get all session_id values from usession table for user "awpeople".

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

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

代码:

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'))); 

推荐答案

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

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

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'))); 

请注意，由于内部查询依赖于 usersession，您应该从它last 中删除.

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

这篇关于尝试使用 SQL 从多个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！