home work help - 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

General SQL Server Forums

New to SQL Server Programming

home work help

Author

Topic

Gmox512
Starting Member

4 Posts

Posted - 2012-04-20 : 18:21:03

Here's the question:
/* 6
Write an UPDATE statement that modifies the InvoiceCopy table. Change TermsID to 2 for each invoice that's
from a vendor with a DeaultTermsID of 2. Use a subquery.
*/

This is a similar example from the book:


UPDATE InvoiceCopy
SET TermsID = 1
WHERE VendorID = (SELECT VendorID 
	FROM VendorCopy	
	WHERE VendorName = 'Pacific Bell')

And, this is my answer:


UPDATE InvoiceCopy
SET TermsID = 2
WHERE VendorID = (SELECT VendorID 
	FROM VendorCopy	
	WHERE DefaultTermsID = 2)

But, I get the following error:


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

I understand that the subquery returns more than 1 value. But I don't see why this is a problem. The example from the book is nearly identical but the subquery does return one value. I guess I don't understand how to update multiple rows

singularity
Posting Yak Master

153 Posts

Posted - 2012-04-20 : 19:25:47

In the example from the book, the subquery only returns one row because there's only one VendorID for the VendorName of 'Pacific Bell.' Your subquery, on the other hand, returns more than one row, which is not acceptable with a = operator. If you change that = to an IN, however, it should work.

Gmox512
Starting Member

4 Posts

Posted - 2012-04-20 : 21:03:36

I tried using the IN operator, it ran but did not change any rows in the InvoiceCopy table even though it said "18 rows were affected". Those are the rows that already had a TermsID of 2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 12:19:55

quote:
Originally posted by Gmox512

I tried using the IN operator, it ran but did not change any rows in the InvoiceCopy table even though it said "18 rows were affected". Those are the rows that already had a TermsID of 2.

then that means there are no rows existing with defaulttermsID value as 2 and termsid with some other value.

what does this return?


SELECT COUNT(*)
FROM InvoiceCopy
WHERE VendorID IN (SELECT VendorID 
	FROM VendorCopy	
	WHERE DefaultTermsID = 2)
AND TermsID <> 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gmox512
Starting Member

4 Posts

Posted - 2012-04-21 : 13:35:39

OK I see now. Thanks for the help! While trying to test my results I never came up with a query that also tested for AND TermsID <> 2. And, I also made some wrong assumptions to begin with.

Gmox512
Starting Member

4 Posts

Posted - 2012-04-21 : 13:37:00

Oh, and btw, your query returned 0. Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 14:29:51

welcome

let me know if you need any more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources