Issue with delete query - SQL Server Forums

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/

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/

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/

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?

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?

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/

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources