Microsoft SQL Server forum - Aggregate column comparison in sql server sql ?

Author

Aggregate column comparison in sql server sql ?

guhar1@yahoo.com

2005-07-27, 8:23 pm

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?

Thanks

David Portas

2005-07-27, 8:23 pm

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

--
David Portas
SQL Server MVP
--

Erland Sommarskog

2005-07-27, 8:23 pm

David Portas (REMOVE_BEFORE_REPLY
ING_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

guhar1@yahoo.com

2005-07-28, 9:24 am

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!

Sponsored Links