Deleting rows that don't exist in another table

[NeffTribe]

Hello,

Without the use of Subselects, there doesn't appear to be a nice way of deleting rows from table 'A' where the key doesn't match the key in table 'B'.

I saw a post from last year that described a way to do this by copying selecting into a temporary table, dropping the original table, and then renaming the temp table.

Is there a better way. It seems to me that a site with a lot of traffic may get errors if a query was made to the table after it was dropped but before the temp table was renamed.

Finally, are Subselects expected in future versions of MySQL?

Thanks,

-David

 

[petersJazz]

1. If its a one time jobb of deleting some rows you can do like this:

select 'delete from a where id=' || a.id || ';'
from a, b left outer join on (a.id = b.id)
where b.id is null;

The result will be something like:

delete from a where id=234;
delete from a where id=342;
delete from a where id=874;
delete from a where id=255;

Paste this text into a gui tool and run.

2. And you can do some programming like:

select a.id
from a left outer join b on (a.id = b.id)
where b.id is null;

for all rows found do {
delete from a where id=aid;
}

 

[NeffTribe]

Option #2 looks like it should work for me. Thanks for the help.

It'll be nice when we see subselects in MySQL.

Again... thanks.

-David