Only one expression can be specified in the ......

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Only one expression can be specified in the ......

Author

Topic

jtmk
Starting Member

5 Posts

Posted - 2008-09-24 : 10:56:52

Hi,

I have the following t-sql that is throwing up the "Only one expression..." error message:

SELECT id
FROM table
WHERE id IN
(
SELECT MAX(id), col1, col2, col3, col4
FROM table
WHERE (id2 IN (1,2,3,4,5,6))
GROUP BY id2, col1, col2, col3, col4
HAVING COUNT(*) > 1
)

Basically, I want to delete the records returned from the sub query but wanted to check with a select prior to deletion.

Any help greatly appreciated.

Thanks

nduggan23
Starting Member

42 Posts

Posted - 2008-09-24 : 10:59:43

Your inner select can only return a single column. as in

SELECT id
FROM table
WHERE id IN
(
SELECT MAX(id2)
FROM table
WHERE (id2 IN (1,2,3,4,5,6))
GROUP BY id2
HAVING COUNT(*) > 1
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 11:01:17

may be this

SELECT m.id
FROM table m
INNER JOIN 
(
SELECT MAX(id) AS id, col1, col2, col3, col4 
FROM table
WHERE (id2 IN (1,2,3,4,5,6))
GROUP BY id2, col1, col2, col3, col4 
HAVING COUNT(*) > 1
)t
ON t.col1=m.col1
AND t.col2=m.col2
AND t.col3=m.col3
AND t.col4 =m.col4
AND t.id=m.id

jtmk
Starting Member

5 Posts

Posted - 2008-09-24 : 11:31:04

That is great - thanks for your help. I removed the multiple columns in the subquery and it worked. What's the theory behind not being able to select multiple columns in a sub-query?

nduggan23
Starting Member

42 Posts

Posted - 2008-09-24 : 11:42:13

WHERE Column IN (1,2, ...) can only deal with single results. having multiple columns in your inner select is like returning a complex array which it cant deal with. ( (1, 2, 3), (1,2,3), (2, 1, 2))

Im sure you can get a better reason from someone with more experience but this is my understanding of it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 11:46:40

quote:
Originally posted by jtmk

That is great - thanks for your help. I removed the multiple columns in the subquery and it worked. What's the theory behind not being able to select multiple columns in a sub-query?

you can select multiple columns in subquery. the only restriction is when you are using it as the target of IN. When used with IN, subquery must return a single column of values which will be compared to column specified on left side of IN.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources