How to do bulk update in SQL Server 2005.

2 Dec 2008 8:07 PM

Chen

Hi all,

Letâ€™s say I have a series of updates, each one having a where clause that
matches exactly one row.

However, if you think about the updates as a group, they form a single
logical operation.

Is there some way to do a â€œbulk updateâ€ that makes this operation more
efficient?

Other than repeatedly calling a stored procedure, how can I do this, my
goals being to make the operation faster and reduce time during which the
table is locked.

Thanks.
Chen

5 Dec 2008 8:14 PM

Daniel Jameson

Chen,

You can do it with a CASE expression in the SET clause:

UPDATE fb
SET
fb.DataColumn = CASE
    WHEN «KeyCondidtion1» THEN «DataExpression1»
    WHEN «KeyCondidtion2» THEN «DataExpression2»
    ...
    WHEN «KeyCondidtionN» THEN «DataExpressionN»
END
FROM dbo.FooBar fb
WHERE («KeyCondidtion1»)
OR («KeyCondidtion2»)
....
OR («KeyCondidtionN»)

--
Thank you,

Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

Show quoteHide quote

"Chen" <C***@discussions.microsoft.com> wrote in message
news:FF33D56E-5DC1-4D25-AE1F-2692605174EF@microsoft.com...
> Hi all,
>
> Let's say I have a series of updates, each one having a where clause that
> matches exactly one row.
>
> However, if you think about the updates as a group, they form a single
> logical operation.
>
> Is there some way to do a "bulk update" that makes this operation more
> efficient?
>
> Other than repeatedly calling a stored procedure, how can I do this, my
> goals being to make the operation faster and reduce time during which the
> table is locked.
>
> Thanks.
> Chen