question

danishamir-0686 avatar image
0 Votes"
danishamir-0686 asked AnuragSharma-MSFT commented

update a table with different values for two max records

Hi,

I have the following table:

CREATE TABLE bidba.anomaly_mon_store
(
ds_time timestamp,
alert_cont smallint
)


and I want to update, preferably with one statement, the value of the aler_cont column with values of 1 and 2 according to the max and max-1 of the ds_time column:

pseudo code would be:

when max - 1 of ds_time -> alert_cont = 1
when max of ds_time -> alert_cont = 2



azure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @danishamir-0686, just wanted to check if below answer helped you or you need more details.

0 Votes 0 ·

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered

Hi @danishamir-0686, welcome to Microsoft Q&A forum.

Please try below query:

 ;WITH CTE as(
 SELECT top 2 *, ROW_NUMBER() OVER(ORDER BY ds_time desc) AS Row_Number  
 FROM anomaly_mon_store)
 UPDATE anomaly_mon_store 
 SET alert_cont = CTE.Row_Number
 from CTE
 WHERE anomaly_mon_store.ds_time = CTE.ds_time;


If answer helps, please mark it 'Accept Answer'




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.