Update multiple records

9 Jun 2006 9:37 PM

Keith Kubicek

> thanks Dan, there is an update trigger on the table. I think I've > confirmed what you suspect by creating a copy of the table, I checked > to make sure there is no trigger, ran the update query and this time > it worked, (95 rows affected). I'm going to take a closer look at the > trigger, what am I looking for? Where can I get a crash course in > triggers :) I've got Murach's SQL for SQL Server and it's got a > section on triggers.
> Keith Kubicek -- Keith Kubicek ------------------------------------------------------------------------ Posted via http://www.codecomments.com ------------------------------------------------------------------------

18 Jun 2006 3:25 AM

Dan Guzman

A common trigger mistake is to assume only one row will be affected. Look
for scalar subqueries that use the inserted or deleted tables. An example
of such a subquery:

UPDATE MyTable
SET LastUpdateTime = GETDATE()
WHERE ID = (SELECT ID FROM inserted)

This can be changed to the form below to allow for multiple rows:

UPDATE MyTable
SET LastUpdateTime = GETDATE()
WHERE ID IN (SELECT ID FROM inserted)

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Keith Kubicek" <Keith.Kubicek.29j***@mail.codecomments.com> wrote in
message news:Keith.Kubicek.29jrof@mail.codecomments.com...
>
>> thanks Dan, there is an update trigger on the table. I think I've
>> confirmed what you suspect by creating a copy of the table, I checked
>> to make sure there is no trigger, ran the update query and this time
>> it worked, (95 rows affected). I'm going to take a closer look at the
>> trigger, what am I looking for? Where can I get a crash course in
>> triggers :) I've got Murach's SQL for SQL Server and it's got a
>> section on triggers.
>> Keith Kubicek
>
>
>
> --
> Keith Kubicek
> ------------------------------------------------------------------------
> Posted via http://www.codecomments.com
> ------------------------------------------------------------------------
>

Update multiple records - SQL