Updating a column based on another column in SQL Server-CodePudding

I have a stored procedure that takes values from an ADF pipeline to write watermark values in a SQL Server table. The new watermark value essentially replaces the old one, leaving no record for the old one.

However, my new requirement is to keep the old watermark value as well.

My current proc that helps update the table is simple.

    ALTER PROCEDURE [dbo].[usp_write_watermark] @LastModifiedtime datetime, @TableName varchar(50)
AS

BEGIN

UPDATE watermarktable
SET [WatermarkValue] = @LastModifiedtime
WHERE [TableName] = @TableName

END

the output im after is like this:

Table	OldWaterMark	NewWaterMark
TableA	22/12/2022	23/12/2022
TableB	21/12/2022	22/12/2022

and so forth. I don't want a growing list, but want NewWaterMark to replace the old watermark when the New one is modified. I hope I am making sense.

Please ask questions if I haven't explained well.

CodePudding user response：

You can do this by just adding one more line to your UPDATE statement. This extra line saves the current watermark into the old watermark field.

UPDATE watermarktable
SET [OldWaterMark] = [NewWatermark],
    [NewWatermark] = @LastModifiedtime  
WHERE [TableName] = @TableName

Note that when you're putting in the first watermark for a table (so that up until then, the newwatermark is NULL), this NULL value is transferred to oldwatermark. Only the second update - where newwatermark already has a value - is a non-NULL value copied to oldwatermark.