Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

SQL DELETE JOIN

  • Last Updated : 25 Oct, 2021

We use joins 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

Step 1: We are creating a Database. For this use the below command to create a database named GeeksforGeeks.

Query:

CREATE DATABASE GeeksforGeeks;

Step 2: To use the GeeksforGeeks database use the below command.

Query:

USE GeeksforGeeks

Output:

Step 3: Now we are creating two tables. Create a table for students with 3 columns and library_books with 2 columns using the following SQL query.

Query:

CREATE TABLE student (
student_id VARCHAR(8),
student_name VARCHAR(20),
student_branch VARCHAR(20)
)

Output:



Query:

CREATE TABLE library_books(
lib_id VARCHAR(20),
book_taken INT
)

Output:

Step 4: Viewing the description of the tables.

Query:

EXEC sp_columns students

Output:

Query:

EXEC sp_columns library_books

Output:



Step 5: The query for Inserting rows into the Table. Inserting rows into students and library_books  table using the following SQL query.

Query:

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')

Output:

Query:

INSERT INTO library_books
VALUES( '1001',2),
( '1002',3),
( '1003',4),
( '2001',2),
( '3001',3)

Output:

Step 6: Viewing the inserted data

Query:

SELECT * FROM students

Output:



Query:

SELECT * FROM library_books

Output:

  • 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:

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!