Updating multiple rows - 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 2005 Forums

Transact-SQL (2005)

Updating multiple rows

Author

Topic

trackjunkie
Starting Member

31 Posts

Posted - 2010-10-13 : 16:40:59

I have the following simple update proceedure:

ALTER Procedure [dbo].[Flex_SetQ/ATriggers] (
@RMANum nvarchar(30)
)
As

Update item
SET charfld2 = 'Y', charfld3 = @RMANum, decifld1 = 3
WHERE item.item = (select top 1 rmaitem.item
from rmaitem
where ltrim(rmaitem.rma_num) = @RMANum)

This works as long as there is only one match on rmaitem, but violates the "=" clause if there are multiple matches. That is why I added the "top 1" for now. How can I get this to update multiple results from rmaitem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-13 : 16:56:25

UPDATE i
SET charfld2 = 'Y', charfld3 = @RMANum, decifld1 = 3
FROM item i
JOIN rmaitem r
ON i.item = r.item
WHERE LTRIM(r.rma_num) = @RMANum

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources