SQL中无法直接用DELETE JOIN删除？用子查询和临时表来搞定！

In SQL, you cannot directly use the `JOIN` clause in a `DELETE` statement to delete records from multiple tables that are related. However, you can achieve similar functionality using subqueries or temporary tables. Here's an example of how you might delete records based on a join condition: 1. Using Subquery: ```sql DELETE FROM Table1 t1 WHERE EXISTS ( SELECT 1 FROM Table2 t2 WHERE t1.id = t2.table1_id AND t2.some_column = 'some_value' ); ``` This will delete rows from `Table1` where the corresponding row in `Table2` has `some_column` equal to `'some_value'`. 2. Using Temporary Table: ```sql CREATE TEMPORARY TABLE IF NOT EXISTS TempTable AS SELECT t1.* FROM Table1 t1 JOIN Table2 t2 ON t1.id = t2.table1_id WHERE t2.some_column = 'some_value'; DELETE FROM Table1 WHERE id IN (SELECT id FROM TempTable); ``` First, it creates a temporary table with the rows from `Table1` that match the condition. Then it deletes those rows from `Table1`. If your goal is to delete records from both tables based on a join condition, you would need to perform two separate `DELETE` statements, one for each table, and use the appropriate subquery or temporary table approach for each. Remember to always back up your data before performing any deletion operations, and consider using transactions to ensure data integrity. ######[AI写代码神器 | 337点数解答 | 2024-07-29 16:12:01]