SQL查询求助：根据表A指定account_id删除表B对应ad_id数据

Got it, let's break this down. You need to first fetch all ad_id values linked to account_id = 100 from Table A, then delete every row in Table B that has those ad_ids. Here are a few reliable, widely-supported approaches to do this in SQL:

Method 1: Using a Subquery Directly in the DELETE Statement #

This is the most straightforward approach and works in most SQL databases (PostgreSQL, MySQL, SQL Server, etc.):

DELETE FROM B
WHERE ad_id IN (
    SELECT ad_id
    FROM A
    WHERE account_id = 100
);

Explanation: #

• The inner subquery SELECT ad_id FROM A WHERE account_id = 100 grabs all ad IDs associated with the target account.
• The outer DELETE statement removes any rows in Table B where the ad_id exists in that list of results.

Method 2: Using EXISTS for Better Performance (Large Datasets) #

If you're working with a lot of data, using EXISTS can be more efficient than IN because it stops checking as soon as a match is found:

DELETE FROM B
WHERE EXISTS (
    SELECT 1
    FROM A
    WHERE A.ad_id = B.ad_id
    AND A.account_id = 100
);

Explanation: #

• For each row in Table B, the EXISTS clause checks if there's a corresponding row in Table A where ad_id matches and account_id is 100.
• If a match exists, that row in B gets deleted. This is often faster for large tables because it avoids creating a full list of ad IDs first.

Method 3: Using a JOIN in DELETE (MySQL-Specific) #

MySQL allows you to use a JOIN directly in the DELETE statement, which can also be efficient:

DELETE B
FROM B
JOIN A ON B.ad_id = A.ad_id
WHERE A.account_id = 100;

Explanation: #

• This joins Table B with Table A on matching ad_ids, then deletes the rows from B where the joined A row has account_id = 100.

Important Notes: #

• Always back up your data or run a SELECT first to verify which rows will be deleted (e.g., SELECT * FROM B WHERE ad_id IN (SELECT ad_id FROM A WHERE account_id=100)).
• If your database has foreign key constraints pointing to B's ad_id, you might need to handle those first (like deleting dependent rows or temporarily disabling constraints).

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