26.6 C
Jaipur
Sunday, June 20, 2021

How MySQL Deletes With the Delete Join Statements – Linux Hint

Must read

This tutorial will discuss how to use the MySQL DELETE clause in a JOIN statement to delete data from multiple tables that meet the specified condition.

The purpose of this tutorial is to help you understand how to use the DELETE clause along with the JOIN clause to remove data at once. If you are new to MySQL, consider our other tutorials, such as JOINS and DROP tables tutorials.

Let us get started.

Basic Usage: Delete With Inner Join

The first Delete method we will discuss is how to use the MySQL DELETE clause inside an INNER JOIN statement to remove data from rows matching another table.

The general syntax to implement such a query is shown below:

Let me assure you that it is easier than it looks. Let me explain:

We start by specifying the tables from which we want to remove the data. Tables are specified between the DELETE and FROM clause.

In the second part, we specify the condition for the matching rows in the set tables. For example:

Finally, we set the WHERE condition that determines the rows in the specified tables to be deleted.

Example Use Case

Let me use an example to illustrate how we can use the DELETE clause and INNER JOIN to remove rows from multiple tables. Consider the queries below:

CREATE SCHEMA society;
USE society;
DROP TABLE IF EXISTS users, contacts;
CREATE TABLE users(
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    state VARCHAR(50)
);
CREATE TABLE contacts(
    home_id INT PRIMARY KEY AUTO_INCREMENT,
    tel VARCHAR(50),
    address VARCHAR(255)
);
INSERT INTO users(first_name, last_name, state) VALUES (“John”, “Muller”, “Colorado”), (“Mary”, “Jane”, “California”), (“Peter”, “Quill”, “New York”);
INSERT INTO contacts(tel, address) VALUES (“303-555-0156”, “281 Denver, Colorado”), (“661-555-0134”, “302 Drive, Bakersfield”), (“516-555-0148”, “626 Est Meadow, NYC”);

Once we have such data, we can illustrate how to use DELETE with INNER JOIN as shown in the query below:

The query above will display the result as shown below:

two rows affected in 7 ms, which indicates that two rows have been removed.

Delete with LEFT JOIN

The second Delete method we will discuss is to use the LEFT JOIN. The general syntax for this delete type is as shown below:

FOR DELETE with LEFT JOIN, we specify only one table—unlike the INNER JOIN where we specified two tables.

Consider the entire query below:

USE society;
DROP TABLE IF EXISTS users, contacts;
CREATE TABLE users(
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    state VARCHAR(50)
);
CREATE TABLE contacts(
    home_id INT PRIMARY KEY AUTO_INCREMENT,
    tel VARCHAR(50),
    address VARCHAR(255)
);
INSERT INTO users(first_name, last_name, state) VALUES (“John”, “Muller”, “Colorado”), (“Mary”, “Jane”, “California”), (“Peter”, “Quill”, “New York”), (“Mystic”, “Arts”, “South Carolina”);
INSERT INTO contacts(tel, address) VALUES (“303-555-0156”, “281 Denver, Colorado”), (“661-555-0134”, “302 Drive, Bakersfield”), (“516-555-0148”, “626 Est Meadow NYC”), (“843-555-0105”, null);
 
DELETE users FROM users LEFT JOIN contacts ON user_id = home_id WHERE address IS NULL;
SELECT * FROM users;

Once we execute the above query, the user whose address is null after the JOIN is deleted and the output result is as shown below:

Conclusion

In this tutorial, we discussed how to use MySQL DELETE with JOIN statements to delete data from multiple tables.

Source link

- Advertisement -

More articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement -

Latest article