473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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?

Thanks

Jul 27 '05 #1
3 1742
DELETE FROM T1
WHERE EXISTS
(SELECT *
FROM T2
WHERE T1.a = T2.c
AND T1.b = T2.d) ;

--
David Portas
SQL Server MVP
--
Jul 27 '05 #2
David Portas (RE****************************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 27 '05 #3
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!

Jul 28 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Steven An | last post by:
Howdy, I need to write an update query with multiple aggregate functions. Here is an example: UPDATE t SET t.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ), t.b = ( select sum(f.q)...
2
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n...
1
by: nfrodsham | last post by:
In Microsoft's help literature, it states: "You can filter out non-unique rows by using the DISTINCT option of an aggregate function" I am trying to do this in Access 2003 with the COUNT...
3
by: MrNobody | last post by:
I've read that the expression property for DataColumns is used to "Sets or retrieves the expresssion used to filter rows, calculate the values in a column, or create an aggregate column.". I...
6
by: Larry Menard | last post by:
Folks, I know that DB2 does not (yet?) support this, but I wonder if anyone can suggest a work-around. I've seen article...
1
by: Scott Gerhardt | last post by:
Hello, I am new to the list, my apology if this question is beyond the scope or charter of this list. My questions is: What is the best method to perform an aggregate query to calculate sum()...
3
by: gobwash | last post by:
Do aggregate functions (sum, count, min, max) inherently cause table locks? More concretely, would the following query typically result in a table lock? select sum(quantity) as total from...
2
by: rhaazy | last post by:
I need to know how I can format a string in C# to get the current date/ time, so that I can do a comparison against a date time column in MS SQL Server 2005. The date/time column in the database...
2
by: edcha | last post by:
I am trying to use the following Aggregate Calculation. string time = trabajadoresTable.Compute ( "Sum(DateTime)" , "") .ToString(); I need to sum the hours in a column with type DateTime. If...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.