SQL Server Forums
Profile | Register | 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
 New Topic  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
30281 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  
 New 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.17 seconds. Powered By: Snitz Forums 2000