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
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);
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;
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.
