Deleting records

[Stefan]

Hello,

Who can help me.

I have 2 tables with the same structure: Main and dummy
Now i want to delete all the records (with the same ID) in the main table if
they are not in de dummy.

example
In main
ID City Street Type
1000 00 00 H1
1001 01 00 H1
1001 01 00 H2
1002 ...

in dummy
ID City Street Type
1001 01 00 H1


So, only the record with "Type H2" must be deleted in the main table.
Wie kan me helpen.

ps. First delete ALL the records with Id =1001 is no option because of
other useful fields.

 

[John W. Vinson]

Hello,

Who can help me.

I have 2 tables with the same structure: Main and dummy
Now i want to delete all the records (with the same ID) in the main table if
they are not in de dummy.

example
In main
ID City Street Type
1000 00 00 H1
1001 01 00 H1
1001 01 00 H2
1002 ...

in dummy
ID City Street Type
1001 01 00 H1


So, only the record with "Type H2" must be deleted in the main table.
Wie kan me helpen.

ps. First delete ALL the records with Id =1001 is no option because of
other useful fields.

Back up your database first!!!

A NOT EXISTS clause should work:

DELETE * FROM Main
WHERE NOT EXISTS
(SELECT Dummy.ID FROM Dummy WHERE Dummy.ID = Main.ID AND Dummy.Type =
Main.Type);

 

[Stefan]

Back up your database first!!!

A NOT EXISTS clause should work:

DELETE * FROM Main
WHERE NOT EXISTS
(SELECT Dummy.ID FROM Dummy WHERE Dummy.ID = Main.ID AND Dummy.Type =
Main.Type);
--

thanks, i'm gonna try this.