How to handle update latest data and improve this stored procedure without using sub-query

MD Akram Alam 0
Viewed 22

I have created a stored procedure to handle, and update the latest createdOn record. This stored procedure is working fine but I wanted to improve it without using sub-query.

Can anyone help me with this?

CREATE PROCEDURE [dbo].[UpdateLatestForecast] 
    @PID INT,
    @CID INT
AS
BEGIN
    UPDATE masterTable
    SET STATE = CASE 
                    WHEN CreatedOn = (SELECT MAX(CreatedOn)
                                      FROM masterTable
                                      WHERE PID = @PID
                                        AND CID = @CID)
                        THEN 'Latest data'
                        ELSE 'old data'
                END
    WHERE PID = @PID
      AND CID = @CID
END
stored-procedures sql-server sql
1 Answers

You can user ROW_NUMBER to order the rows by CreatedOn and then update based on that.

WITH CTE_masterTable AS 
(
   SELECT *, ROW_NUMBER() OVER (ORDER BY CreatedOn DESC) RN
   FROM masterTable
   WHERE PID = @PID
   AND CID = @CID   
)
UPDATE CTE_masterTable 
SET STATE = CASE WHEN RN =1 
                 THEN 'Latest data'
                 ELSE 'old data'
            END

OR..

you can add partiton by CID and PID, and filter in update. With this approach you can maybe even get rid of @PID and @CID input parameters and just set for every PID and CID in one go:

WITH CTE_masterTable AS 
(
   SELECT *, ROW_NUMBER() OVER (PARTITION BY PID, CID ORDER BY CreatedOn DESC) RN
   FROM masterTable
)
UPDATE CTE_masterTable 
SET STATE = CASE WHEN RN =1 
                 THEN 'Latest data'
                 ELSE 'old data'
            END
--WHERE PID = @PID
--AND CID = @CID
Nenad Zivkovic0
Related