Dynamic update from select result

Question

Hi

How can I turn each row retuned by a SELECT query into a dynamic UPDATE statement and execute one by one?

Thanks

Regards

Answer 1

Hi,

How can I turn each row retuned by a SELECT query into a dynamic UPDATE statement and execute one by one?

Option one: use a Cursor

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/cursors-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

What you describe fits exactly to the use of a Cursor. You can DECLARe a Cursor for the SELECT query which you want. Next you can go row by row using the 'FETCH NEXT FROM' your cursor, and for each row you use UPDATE query

You can also check this post on using cursors

https://www.mssqltips.com/sqlservertip/1599/cursor-in-sql-server/

Note! SQL Server is a tabular server which is bst working on SET of rows and NOT row by row. For moist cases I would HIGHLY recommend NOT to use a cursor or any other type of loop. Instead you can use the next option (which does not cover the requirement for "one by one" but will probably provide better solution

Option two: using UPDATE from SELECT

Use something like the following sam ple:

create table TargetTbl (c1 int, c2 int)
GO
create table SourceTbl (c1 int, c2 int)
GO
INSERT TargetTbl(c1,c2) values (1,1)
INSERT SourceTbl(c1,c2) values (1,2)
GO
SELECT * FROM TargetTbl
SELECT * FROM SourceTbl
GO

update TargetTbl
set c2 = s.c2
from SourceTbl s
GO
SELECT * FROM TargetTbl
SELECT * FROM SourceTbl
GO

Answer 2

Hi @-- --

Try this:

;WITH CTE AS
(
 SELECT A.[StartTime] AS New_StartTime, A.[EndTime] AS New_EndTime,
        B.[StartTime], B.[EndTime]
 FROM [dbo].[StaffBookings] A JOIN RemoteStaffBookings B ON A.[ID]=B.[ID]
)
UPDATE CTE 
SET StartTime = New_StartTime, EndTime = New_EndTime

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Obviously, if the tables are in the same instance, there are more efficient ways, but I take it that RemoteStaffBookings is in a different place, and you want to copy the data. Hopefully, the below will work for you, but I need to add the exclaimer that since I don't have your tables, I have not been able to test it.

SELECT 'UPDATE RemoteStaffBookings
        SET    StartTime = ' + quotename(convert(char(23), StartTime, 126), '''') + ',
               EndTime   = ' + quotename(convert(char(23), EndTime, 126), '''') + '
        WHERE  ID = ' + convert(varchar(10), ID)       
FROM   [dbo].[StaffBookings]

I should also add that the above is good, if you only have something a few hundred rows. If you have lot more, it's probablyl better to generate an XML or JSON document, and perform a single update from that document.