update set clause statement - 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

SQL Server 2000 Forums

Transact-SQL (2000)

update set clause statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-07-12 : 11:18:19

Karen writes "I am trying to populate a field (LCP)

update USR_INVENTOVERSTOCK6MO
SET LCP=
(SELECT INVENTTABLEMODULE.PRICE
FROM INVENTTABLEMODULE
inner JOIN USR_INVENTOVERSTOCK6MO
ON (INVENTTABLEMODULE.ITEMID = USR_INVENTOVERSTOCK6MO.ITEMID
and INVENTTABLEMODULE.dataareaid = 'usa' and INVENTTABLEMODULE.MODULETYPE = 0)
WHERE USR_INVENTOVERSTOCK6MO.ITEMID = INVENTTABLEMODULE.ITEMID )

I get this error message
Server: Msg 512, Level 16, State 1, Line 1
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.

the inventtablemodule has duplicate values but the dataareaid = 'usa' and moduletype=0 isolates the one value I want, so I guess I just don't know how to reconstruct this statement so it will pull just the one value (LCP, lastcostprice)

thank you in advance for any help.

Karen"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-12 : 11:25:12

try
update USR_INVENTOVERSTOCK6MO
SET LCP= INVENTTABLEMODULE.PRICE
from USR_INVENTOVERSTOCK6MO
JOIN USR_INVENTOVERSTOCK6MO
ON INVENTTABLEMODULE.ITEMID = USR_INVENTOVERSTOCK6MO.ITEMID
and INVENTTABLEMODULE.dataareaid = 'usa'
and INVENTTABLEMODULE.MODULETYPE = 0

also try
select ITEMID
from INVENTTABLEMODULE
where INVENTTABLEMODULE.dataareaid = 'usa'
and INVENTTABLEMODULE.MODULETYPE = 0
group by ITEMID
having count(*) > 0

to find the ones that might cause problems

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources