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 2008 Forums
 Transact-SQL (2008)
 Issue with delete query

Author  Topic 

taunt
Posting Yak Master

128 Posts

Posted - 2015-04-20 : 13:00:50
Hello I'm running this query:

DELETE FROM Vendortemp
WHERE (ActionCode = 'C') AND (LabelCode =
(SELECT TOP (100) PERCENT LabelCode
FROM Vendortemp AS Vendortemp_1
GROUP BY LabelCode
HAVING (COUNT(*) > 1)
ORDER BY LabelCode))


and get this error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So how can I format the query to delete multiple results?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-20 : 13:20:38
You just need to swap the = to IN for LabelCode, but why are you using TOP 100 PERCENT?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-20 : 13:24:53
Or this:


WITH v2 (LabelCode)
AS
(
SELECT LabelCode
FROM Vendortemp
GROUP BY LabelCode
HAVING (COUNT(*) > 1)
)
DELETE v1
FROM Vendortemp v1
JOIN v2 ON v1.LabelCode = v2.LabelCode
WHERE v1.ActionCode = 'C'


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2015-04-20 : 14:10:10
Replacing = with in worked like a charm.

Thanks for your help.

quote:
Originally posted by tkizer

You just need to swap the = to IN for LabelCode, but why are you using TOP 100 PERCENT?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-21 : 06:05:56
Did you take the:

TOP (100) PERCENT

and

ORDER BY LabelCode

out of the inner SELECT?
Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2015-04-21 : 11:32:24
Nope I left them in and it still worked. SQLSMS adds things to the query and "TOP (100) PERCENT" was one of them. I copied from a select query that's why the order was left over.

Thanks

quote:
Originally posted by Kristen

Did you take the:

TOP (100) PERCENT

and

ORDER BY LabelCode

out of the inner SELECT?

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-21 : 12:32:31
Don't let SSMS build your queries as it does silly stuff. Take out the TOP 100 PERCENT and ORDER BY.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-21 : 12:33:02
Better to take them out as they are superfluous and

TOP (100) PERCENT and ORDER BY

(in VIEWS) are regarded as bad practice.
Go to Top of Page
   

- Advertisement -