PDA

View Full Version : deleting records in MySQL table...



qmqmqm
March 26th, 2009, 08:50 PM
I have some tables in a database. The fields of each table are like this:
Table 1: id, value
Table 2: table1_id, value

I wish to perform kind of a "clean-up" to the datebase: delete all entries in Table 1 if it is not in table 2.

Does anyone know the syntax to do this?

Thanks,

Tom

kpatz
March 26th, 2009, 08:56 PM
Table 1: id, value
Table 2: table1_id, value

The standard (ANSI SQL) way of doing it is:


delete from table1
where not exists
(select * from table2 where table1.id = table2.table1_id);


There's a MySQL syntax that works too:



DELETE table1
FROM table1
LEFT JOIN table2
on table1.id = table2.table1_id
where table2.table1_id is null;

qmqmqm
March 26th, 2009, 11:05 PM
Thank you kpatz !