SQL DELETE JOIN
DELETE JOIN in SQL lets you delete rows of a table, based on conditions involving another table. We can use the DELETE statement with the JOIN operation to perform DELETE JOIN.
We use JOIN to combine data from multiple tables., to delete the same rows or related rows from the table at that time we use delete join.
In this article let us see how to delete multiple data using DELETE using JOIN by using MSSQL as a server.
Syntax
DELETE table1
FROM table1 JOIN table2
ON table1.attribute_name = table2.attribute_name
WHERE condition
Demo SQL Database
For this DELETE JOIN tutorial, we will use the following two tables in examples:
Table 1- Student
student_id | student_name | student_branch |
---|---|---|
1001 | PRADEEP | E.C.E |
1002 | KIRAN | E.C.E |
1003 | PRANAV | E.C.E |
2001 | PADMA | C.S.E |
2002 | SRUTHI | C.S.E |
2003 | HARSITHA | C.S.E |
3001 | SAI | I.T |
3002 | HARSH | I.T |
3003 | HARSHINI | I.T |
Table 2- Library books
lib_id | book_taken |
---|---|
1001 | 2 |
1002 | 3 |
1003 | 4 |
2001 | 2 |
3001 | 3 |
To create these tables on your system, write the following queries
CREATE DATABASE w3wiki;
USE w3wiki
CREATE TABLE student (
student_id VARCHAR(8),
student_name VARCHAR(20),
student_branch VARCHAR(20)
)
CREATE TABLE library_books(
lib_id VARCHAR(20),
book_taken INT
)
INSERT INTO students
VALUES( '1001','PRADEEP','E.C.E'),
( '1002','KIRAN','E.C.E'),
( '1003','PRANAV','E.C.E'),
( '2001','PADMA','C.S.E'),
( '2002','SRUTHI','C.S.E'),
( '2003','HARSITHA','C.S.E'),
( '3001','SAI','I.T'),
( '3002','HARSH','I.T'),
( '3003','HARSHINI','I.T')
INSERT INTO library_books
VALUES( '1001',2),
( '1002',3),
( '1003',4),
( '2001',2),
( '3001',3)
SQL DELETE JOIN Example
Query to delete library entry for id 1001 using JOIN
Query:
DELETE library_books FROM library_books JOIN students ON students.student_id =library_books.lib_id WHERE lib_id= 1001 SELECT * FROM library_books
Output:
Key Takeaways about DELETE JOIN
- DELETE JOIN allows to delete rows from a table based on condition involving another table.
- We can use DELETE with JOIN to delete multiple rows from two or more tables.
- Using WHERE clause with JOIN allows to specify condition for deleting rows.
- If a record is deleted from a table, related records in other table will be deleted too
Contact Us