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.
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 |
|
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. |
 |
|
|
|
|