does this call for a cursor? how?

Hi, sorry I am kind of stuck how to do this correctly.
The following is my problem statement:

There are three tables: TAB1 TAB2 TAB3

There is a 1 to many relationship between TAB2 and TAB1.

For every row in TAB2 that has a match in TAB1, they are related via
TAB2.col3 = TAB1.col2

I want to delete all those rows in both tables that match, but I have to
delete those rows in TAB1 first because of dependency constraints.
The primary key for TAB2.col1

Therefore I need to have a select block first and store it somewhere(?)

select
TAB2.col1
from
TAB2
where
TAB2.col3 IN
(select
TAB1.col2
from
TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))

Then I delete all those rows in TAB1 that has a match in TAB3

delete from TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))

now i delete those rows in TAB2 from the result set of the first sql block.

thanks for any help.Hi, sorry I am kind of stuck how to do this correctly.
The following is my problem statement:

There are three tables: TAB1 TAB2 TAB3

There is a 1 to many relationship between TAB2 and TAB1.

For every row in TAB2 that has a match in TAB1, they are related via
TAB2.col3 = TAB1.col2

I want to delete all those rows in both tables that match, but I have to
delete those rows in TAB1 first because of dependency constraints.
The primary key for TAB2.col1

Therefore I need to have a select block first and store it somewhere(?)

select
TAB2.col1
from
TAB2
where
TAB2.col3 IN
(select
TAB1.col2
from
TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))

Then I delete all those rows in TAB1 that has a match in TAB3

delete from TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))
now i delete those rows in TAB2 from the result set of the first sql block.

thanks for any help.|||wonder if changing the RI / Relationship to "Delete Cascade" would help?

there are times and places where delete cascade isnt that smart, but it may well be appropriate here.

btw I think you ought to rethink your table design... calling them tab1,2,3 & Col1,2,3 is just being masochistic (unless you are trying to hide the nature of the table here which is fine...)|||Hi, sorry I am kind of stuck how to do this correctly.
The following is my problem statement:

There are three tables: TAB1 TAB2 TAB3

There is a 1 to many relationship between TAB2 and TAB1.

For every row in TAB2 that has a match in TAB1, they are related via
TAB2.col3 = TAB1.col2

I want to delete all those rows in both tables that match, but I have to
delete those rows in TAB1 first because of dependency constraints.
The primary key for TAB2.col1

Therefore I need to have a select block first and store it somewhere(?)

select
TAB2.col1
from
TAB2
where
TAB2.col3 IN
(select
TAB1.col2
from
TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))

Then I delete all those rows in TAB1 that has a match in TAB3

delete from TAB1
where
TAB1.col1 IN
(select
TAB3.col1
from
TAB3))
now i delete those rows in TAB2 from the result set of the first sql block.

thanks for any help.|||hi healdem,

thanks for the reply, and yes I am trying to hide the table names... btw, I have transferred this query to the sql forum.

thanks again... g11DB|||If not really sure ...but

DELETE FROM Table3 WHERE Exists (SELECT * FROM Table2 Correlate)|||Dupe thread from Access forum merged into this one

:)