how to preform multiple conditions select?

[geramis]

how do i write the following sql in sql-server 7.0?????? (it works in ORACLE)

DELETE FROM tbl2
WHERE (fld1,fld2) = ANY (SELECT fld1,fld2 FROM tbl1);


[*** i want to delete from tbl2 all the rows with
corresponding values in tbl1 ****]

 

[kim1]

Hi

This is an equivalent in SQL Server:

DELETE tbl2
WHERE fld1 IN (select fld1 from tbl1)
OR fld2 IN (select fld2 from tbl1)

Depending if you want and AND or OR with the condition of the fld1 and fld2.

You can also split in 2 DELETES statements. Usually one delete statement is more performant than 2 deletes statements

Kim

 

[geramis]

the 2 SQL statements aren't equivalent:
say tbl1 looks like this:
fld1 | fld2
-----------
1 | 2
3 | 4

your sql will delete the record 1,4 from tbl2
while my sql won't !!!
i need to delete only matching records
(in the above example (1,2) Or (3,4))

 

[kim1]

Try this one:

DELETE tbl2
from tbl1
where fld1 = tbl1.fld1
and fld2 = tbl1.fld2

Kim

 

[geramis]

it works although it is syntacticly weird
{ i would expect rows from tbl1 to be deleted }

i think the ORCALE way is clearer, BUT..

thanks a LOT, you've helped !!!

 

[tlbroadbent]

I would write the SQL Server query as follows.

DELETE tbl2
from tbl2 Inner Join tbl1
On tbl2.fld1 = tbl1.fld1
and tbl2.fld2 = tbl1.fld2

An alternate query, somewhat similar to the Oracle query you began with, would be.

Delete From tbl2
Where Exists
(Select * from tbl1
Where fld1=tbl2.fld1
And fld2=tbl2.fld2)
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.