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: delete, join, MySQL, SQL

This entry was posted on September 16th, 2011 and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or Trackback from your own site.