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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 UPDATE
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ori_davidson
Starting Member

15 Posts

Posted - 08/24/2014 :  03:44:55  Show Profile  Reply with Quote
hello,
I am trying to update a field from a list of items:

update OITM
set PicturName = (select DANA from dbo.test)
where ItemCode in (select itemcode from dbo.test)

the ERROR I get:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I understand the problem, but I don't know how to solve it.

I would like that every itemcode from TEST will get the FIELD DANA.

THANKS
ORI

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 08/24/2014 :  04:19:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The first obvious fix would be to use
SET PictureName = (SELECT TOP(1) DANA from dbo.Test)
but then you still have no correlation between ItemCode and PictureName/DANA.
Try this
UPDATE		w
SET		w.PictureName = t.DANA
FROM		dbo.OITM AS w
INNER JOIN	dbo.Test AS t ON t.ItemCode = w.ItemCode;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

ori_davidson
Starting Member

15 Posts

Posted - 08/24/2014 :  04:51:02  Show Profile  Reply with Quote
Thank you very much for your answer.
Now I'm getting this Error:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'PictureName'.
Go to Top of Page

ori_davidson
Starting Member

15 Posts

Posted - 08/24/2014 :  05:43:39  Show Profile  Reply with Quote
IT WORKS - THANKS AGAIN!!!


quote:
Originally posted by SwePeso

The first obvious fix would be to use
SET PictureName = (SELECT TOP(1) DANA from dbo.Test)
but then you still have no correlation between ItemCode and PictureName/DANA.
Try this
UPDATE		w
SET		w.PictureName = t.DANA
FROM		dbo.OITM AS w
INNER JOIN	dbo.Test AS t ON t.ItemCode = w.ItemCode;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000