DELETE data from a table by joining with another table in SQL

  • How-Tos FAQs
  • December 18, 2018
DELETE data from a table by joining with another table in SQLSupercharge Your Snowflake SQL
with Datameer's
Data Transformation

We can join multiple tables in the DELETE statement, just like in the SELECT statement.

DELETE data from a table by joining with another table in SQL

Let us consider the below tables.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATETIME,
    total_orders INT
);
INSERT INTO  orders
SELECT 1,'Jack', '2020-02-03', 4 UNION ALL
SELECT 2, 'Rose', '2020-01-09', 19;

CREATE TABLE order_details (
    order_detail_id INT PRIMARY KEY,
    order_id VARCHAR(100),
    item VARCHAR(100)
);

INSERT INTO  order_details
SELECT 1,1, 'laptop' UNION ALL
SELECT 2,1, 'camera' UNION ALL
SELECT 3,1, 'headphone' UNION ALL
SELECT 4,2, 'mouse';

-- Here, data of table order_detail is deleted checking the value of column from order table using INNER JOIN

DELETE od
FROM order_details od
INNER JOIN  orders o
       ON o.order_id = od.order_id
WHERE o.customer_name = 'Jack';

-- LEFT JOIN also works just fine
DELETE od
FROM order_details od
INNER JOIN  orders o
       ON o.order_id = od.order_id
WHERE o.customer_name = 'Jack';

Up Next:

Read How to select dates between the two given dates in SQL?