SQL delete using multiple "where" columns

[ralc]

Hi

I have 2 tables :
t1 is the main data table with 3 fields
t2 is a driver table which will drive the delete

t1 t2
========== ======
f1 f2 f3 f1 f2
a 1 do a 1
a 2 re b 2
b 2 mi c 5
c 2 fa
c 3 so

I want to delete all rows from t1 where the f1 AND f2 field values exist in t2 (i.e. rows 1 and 3)

A select would go like this :
select * from t1 A, t2 B where A.f1=B.f1 and A.f2=B.f2

Unfortunately this does not work for delete. The closest I though might work was :
delete from t1 where t1.f1=t2.f1 and t1.f2=t2.f2

I looked at subqueries :
delete from t1 where
f1 in (select f1 from t2) and
f2 in (select f2 from t2)

However this would just delete any rows with an f1=a or b or an f2=1 or 2. Including a,2 and c,2 which do NOT exist in driver table and should not be deleted.

This is quite urgent as per usual I have people on my back. Help MUCH appreciated.

 

[qyllr]

hello ralc,

i worked on a similar project before...
i used perl to generate my delete script...

here are the steps:

1. run your query and select the columns you need from t2 and send the output to an out file...

2. write a perl script that would generate your delete sql...using the out file for your where condition from your initial query.

3. back up the data from t1...use bcp or select into back up db...

4. using isql again run the query produced by the perl script.

if you want to take this route, email me so i can send you the perl script...

qyl1r@aol.com

later,
q.

 

[ralc]

Thanks for your help there.

I found another solution to the problem (at 2am !!)

delete from t1 from t1 A, t2 B
where A.f1=B.f1 and A.f2=B.f2

This "first from clause", "second from clause" was new to me but it seems work. The "second from" allows the target of the delete to be modified from the results of a where query.


P.S.
Another trick I tried was to use views :

create view v1 as select A.* from
t1 A, t2 B where A.f1=B.f1 and A.f2=B.f2

This failed as the view's from clause consisted of more than one table. Damnit.

Anyway thanks again.

 

[Tim1]

Hiya,

Why don't you just try the more simple:

DELETE t1
FROM t1 A,
t2 B
WHERE A.f1 = B.f1
AND A.f2 = B.f2

Saves much beating around the bush.

Tim

 

[sybdba]

Hi ralc,

try this...

delete from t1
where f1 in
( select f1 from t2
where t1.f1=t2.f1 and t1.f2=t2.f2 )

I hope that this will useful.

Regards,

Rajdeep

 

[Guest_imported]

just to give an idea,
use dynamic sql,
write a select statement in the 2nd table to genratea delete statement on the first table and at the end execute the genrated statement.


if you still need it , give me time, I will give you the sql for SYBASE