DELETE Syntax Help

jaylee

Suppose I have the following two tables, how do I delete all the entries in table1 that is not in table2?

In this case, I want to delete id 04 and 05 from table1.

(table1)
id name

01 aaa
02 bbb
03 ccc
04 ddd
05 eee

(table2)

id

01
02
03

Anon

DELETE FROM table1 WHERE id NOT IN (SELECT id FROM table2);

jaylee

I just tried it, and it didn't seem to work.

I got an error of:

You have an error in your SQL syntax near 'SELECT id FROM table2)'

By the way, the data type of id is INT, is that going to matter?

Anon

It sounds like your using mysql.

mysql doesn't support nested selects

jaylee

yes, i'm using mysql.

is there any other way to solve this problem or by other syntax to achieve the same goal?

Anon

Oops, I should have mentioned that. I don't know of anyway to write a single query in MySQL that will do this. The only option that I can think of right now is to select everything from table2 then build a comma separated list for the delete query.

select id from table2;

//using above dataset build delete query

delete * from table1 where id not in (1,3,4);

Where 1,3,4 is the data from the first query.

jaylee

This sounds logical to me, but how do I build a comma separated list from the select query?

Thank you.

Anon

I don't think there is a way to do it in mysql with a single SQL command.

Anon

Depends on how you are most comfortable in handeling the data. I've built and use a database abstraction class so I don't use the mysql_ functions so I can't give you the code easily (I'd have to look it all up), but the basics are this.

$list = "";

while ($row = getnextrow()) {
$list .= $row . ",";
}

$list = substr($list, 0, (strlen($list) - 1)); // to remove last comma

$new_query = "DELETE FROM table1 WHERE id NOT IN ($list)";

jaylee

Thank you very much 🙂