Is this possible without a cursor?

I have something like

update table

set field = ...

where field = ...

and for each entry that was effected by this query I want to insert an entry into another table.

I have always done this with cursors is there a more effecient way? For some reason cursors run a lot slower on my sql2005 server than the sql2000 server...

im not sure if this is what you want but maybe something like this... well first of all you cant do two updates on two tables in the same query so you would have to do it in a sproc (am i right?)

so it would be like

updated table2

set field = "my child"

where field in (select field1

from table1

where field = "something")

update table 1

set field ="my parent"

where field = "something"

is that right? sorry i dont know the answer off the top of my head just trying to help

|||

I believe you are looking for the output clause of the update statement:

USE AdventureWorks;GODECLARE @.MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, ModifiedDate datetime);UPDATE TOP (10) HumanResources.EmployeeSET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.ModifiedDateINTO @.MyTableVar;--Display the result set of the table variable.SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDateFROM @.MyTableVar;GO--Display the result set of the table.--Note that ModifiedDate reflects the value generated by an--AFTER UPDATE trigger.SELECT TOP (10) EmployeeID, VacationHours, ModifiedDateFROM HumanResources.Employee;GO