DELETE with JOIN

This article will explain how to delete data from one table based on data in another table – how to use JOIN in DELETE queries.

If you want to delete data from one table based on data in another table, you can always use JOIN syntax. For example, let’s take two tables. First table called people will hold all the example names and ages we have. Create SQL for people table looks like this:

CREATE TABLE `people` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(25) NOT NULL,
	`age` TINYINT(25) UNSIGNED NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=MyISAM

Now, let’s create an example table attending, which will reference the people table by people_id column. Create syntax for attending table:

CREATE TABLE `attending` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`people_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`comment` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=MyISAM

For example to work, let’s add some data into our tables:

insert into people (name, age) values ('John', 20),('Kim', 30),('Barry', 40)
insert into attending (people_id, comment) values(1,'Comment'),(2,'Comment'),(3,'Comment')

After we’ve inserted the data, our tables look like this:

people
id name age
1 John 20
2 Kim 30
3 Barry 40
attending
id people_id comment
1 1 Comment
2 2 Comment
3 3 Comment

So for example, to delete people from attending table that are older than 31 years (based on age column in people table), we would write a query like this:

DELETE attending FROM attending
JOIN people ON people.id = attending.people_id
WHERE
people.age > 31

To check the data after this query, let’s issue this query:

SELECT people.* FROM attending
JOIN people ON people.id = attending.people_id

And the output should be the same is the table below:

id name age
1 John 20
2 Kim 30

We see that Barry is no longer in attending table. This is a quick and simple example on how to use JOIN with DELETE queries in MySQL.


Tags: , , ,

 
 
 

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close