How to Delete Rows from One Table Based on Matching IDs in Another Using SQL JOIN?

Deleting Rows Based on Matching IDs Using JOIN

In database management, it's often necessary to delete rows from a table based on criteria related to another table. One such scenario involves deleting all rows in a table whose IDs match those in another table.

To achieve this using a SQL JOIN, the following query can be employed:

DELETE t1 
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID;

In this query:

• DELETE t1 specifies that rows will be deleted from the Table1 table.
• FROM Table1 t1 indicates that the data will be sourced from the Table1 table, aliased as t1.
• JOIN Table2 t2 ON t1.ID = t2.ID establishes a join condition between Table1 (aliased as t1) and Table2 (aliased as t2), matching rows where the ID column values are identical.

By combining these components, the query effectively identifies and deletes all rows in Table1 whose ID values exist in Table2.

It's important to note that using an alias in the DELETE statement, as shown in the query above, is recommended as a safeguard against accidentally deleting the entire table. By failing to highlight the complete query and accidentally running only the DELETE statement, the specified table could be wiped out.

The above is the detailed content of How to Delete Rows from One Table Based on Matching IDs in Another Using SQL JOIN?. For more information, please follow other related articles on the PHP Chinese website!