如何基于另一张表的字段值删除目标表中的数据行

Hey there, let's work through how to delete the right rows from Table1 using the Admin flag in Table2. The core idea is to match users across both tables and remove entries where the user is marked as an admin. Below are implementations for the most common SQL databases:

MySQL/MariaDB #

Use a DELETE JOIN to link the tables and target the rows to remove:

DELETE t1
FROM Table1 t1
JOIN Table2 t2 ON t1.UserID = t2.UserID
WHERE t2.Admin = TRUE;

This joins Table1 and Table2 on matching UserIDs, then deletes only the rows from Table1 where the corresponding user is an admin.

SQL Server #

You have two solid options here. First, a subquery approach that identifies admin users first:

DELETE FROM Table1
WHERE UserID IN (
    SELECT UserID
    FROM Table2
    WHERE Admin = TRUE
);

Or use an inner join for better performance with large datasets:

DELETE t1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.UserID = t2.UserID
WHERE t2.Admin = TRUE;

PostgreSQL #

PostgreSQL supports a USING clause for this scenario, which is clean and efficient:

DELETE FROM Table1 t1
USING Table2 t2
WHERE t1.UserID = t2.UserID
AND t2.Admin = TRUE;

Alternatively, the subquery method from SQL Server works here too if you prefer that syntax.

Important Notes #

• Always verify first: Before running the delete, run a SELECT to confirm which rows will be removed. For example, in MySQL:

  SELECT t1.*
  FROM Table1 t1
  JOIN Table2 t2 ON t1.UserID = t2.UserID
  WHERE t2.Admin = TRUE;
  

• Boolean value variations: If your database uses numeric values for booleans (like 1 for TRUE and 0 for FALSE), just replace TRUE with 1 in the queries above.

内容的提问来源于stack exchange，提问作者Alytas