SQL Standard

SQL Delete with Join

In this article, we will discuss how we can use a SQL join with a delete statement.

Syntax

The syntax for using a delete statement with a SQL join can be expressed as shown:

 DELETE table1
FROM table1 JOIN table2 ON
table1.attribute_name = table2.attribute_name
WHERE condition

Sample Tables

Let us start by creating sample tables and data as shown in the provided queries.

CREATE TABLE developers(
    id serial,
    name VARCHAR(50),
    department VARCHAR(20)
);
CREATE TABLE code_base(
    repo_id VARCHAR(255),
    commit INT
);
INSERT INTO developers(name, department)
VALUES
('Taylor Williams', 'Game developer'),
('Linda Moore', 'Full-Stack Developer'),
('Mary Smith', 'Database Developer');

INSERT INTO code_base(repo_id, commit)
VALUES
('repo_id_1', 1),
('repo_id_2', 2),
('repo_id_3', 3);

The above queries create two tables and add sample data.

Example

To delete a row in the code_base table with the commit value of 1 using a join, we can run a query as shown:

DELETE code_base
FROM code_base, JOIN developers ON
developes.id = code_base.commit
WHERE commit = 1;

The query above should remove the row where the commit value is 1.

Closing

This short article illustrates how to use a delete statement within a join query.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list