multiple columns in "in" search

Is it possible to put a set of columns into a select statement using "in"

For example:

select count(*) from table1 where (col1, col2, col3, col4) in (select a.col1, a.col2, a.col3, a.col4 from table1 a where........)

This is possible in this form for Oracle and DB2 but I cannot get it to work in Sql Server.

Any ideas?

No. SQL Server doesn't support row value constructors yet. You can use a correlated sub-query instead like:

select count(*) from table1 as b where exists (select * from table1 a

where a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3 and a.col4 = b.col4)

|||

Thanks, that solves part of my problem.

What I want to be able to do is to delete using the same (similar) syntax, but from an earlier seperate post I cant prefix a table in a delete query!?!?

My select query is:

select count(*) from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

The way it works is that every person can be registered for a specific language and a specific course. They can sit am exam for a specific course and language as many times as they want, but they get a new entry in the datas1 table but with an incremented exam number from the previous entry. All other values can be the same. Now we want to be able to delete some of the older rows from the datas1 table (the earlier exam entries and keep the most recent ones up to a number specified in the NUMBER variable)

For example, a customer 1001 has sat language 1 in course 2 exam 5 times. If I set the NUMBER variable to 3 then all I want to remain in the datas1 table is entries for exam 3, 4 and 5
The sql above will work as a select but cannot get it to work when trying to do the delete .eg.

delete from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

Please help

|||

You need to use the TSQL extension for the DELETE statement. ANSI SQL doesn't allow aliasing of source table in the DELETE statement. You can modify your DELETE statement like:

delete from schema.datas1 /* note additional FROM clause here */

from schema.datas1 as a where exists
(select * from schema.datas1 z where
z.exam=a.exam and
z.customer_id=a.customer_id and
z.language_id=a.language_id and
z.course_id=a.course_id)
and a.exam <=
(select max(b.exam)-NUMBER from schema.datas1 b where
b.customer_id = a.customer_id and
b.course_id = a.course_id and
b.language_id = a.language_id
)

|||

That works great. Thanks!

One more question.

Now I want to break my deletes from the datas1 table into batches of say 1000 records at a time using a loop statement in perl. What exactly would be the transact sql (I assume using top) to do this?

Again I can get this to work for the select statement, but having difficulty with the delete.

Many Thanks!

|||

In SQL Server 2005, you can also specify TOP clause for DML statements. So you can run a batch like below which will delete 1000 rows at a time:

while(1=1)

begin

delete top(1000) from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

-- you can parameterize the TOP clause like:

declare @.n int

set @.n = 10000

while(1=1)

begin

delete top(@.n) from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

In older versions of SQL Server, you can do the same using SET ROWCOUNT like.

-- you can parameterize the TOP clause like:

declare @.n int

set @.n = 10000

set rowcount @.n

while(1=1)

begin

delete from schema.datas1

from schema.datas1

.....

if @.@.rowcount = 0 break

end

set rowcount 0