Aggregate column comparison in sql server sql ?

I am no expert in sql, but I keep stubbling on this problem:

I have a table t1 with 2 columns (a,b)
I have a table t2 with 2 columns (c,d)

I need to delete all records from t1 which have the same value (a,b)
than the value of (c,d) in all records in the t2 table.

I oracle, this is simple:

delete from t1
where (a,b) in (select c,d from t2)

because Oracle has support for this syntax. Dont remember how they call
it. But this is not support in sql server. So I have to resort to:

delete from t1
where a + '+' + b in ( select c + '+' + d from t2)

Of course, a,b,c,d must be varchar for this to work. Basically I fake a
unique key for the records. Is there a better way to do this?

ThanksDELETE FROM T1
WHERE EXISTS
(SELECT *
FROM T2
WHERE T1.a = T2.c
AND T1.b = T2.d) ;

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> DELETE FROM T1
> WHERE EXISTS
> (SELECT *
> FROM T2
> WHERE T1.a = T2.c
> AND T1.b = T2.d) ;

Which should be added, is a syntax that also works in Oracle.

Then again the syntax with IN that Oracle has is, as far as I know,
ANSI-compliant, so SQL Server is at fault here.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Which re-inforce our local beleive:

Oracle supperior SQL and performance
SQL Server, superior tools for users.

Everybody predicts Oracle downfall because of their reluctance to give
tools like TOAD for free.

The corporate battle must go on!