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 2012 Forums
 Transact-SQL (2012)
 UPDATE

Author  Topic 

ori_davidson
Starting Member

15 Posts

Posted - 2014-08-24 : 03:44:55
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

30421 Posts

Posted - 2014-08-24 : 04:19:10
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 - 2014-08-24 : 04:51:02
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 - 2014-08-24 : 05:43:39
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
   

- Advertisement -