SQL Server Update Latest Records Only in a Group

Question

I'm using SQL Server 2008 R2 and I have 2 tables Product and Product_Master. I want to update only the latest version of records in Product_Master from Product table leaving earlier versions as it is.

CREATE TABLE [dbo].[Product]
(
    [ProdId] [nvarchar](50) NOT NULL,
    [ProdDesc] [nvarchar](50) NULL,
    [ProdPrice] [decimal](18, 0) NULL,
    [Version] [int] NOT NULL
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Product_Master]
(
    [ProdId] [nvarchar](50) NOT NULL,
    [ProdDesc] [nvarchar](50) NULL,
    [ProdPrice] [decimal](18, 0) NULL,
    [Version] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Product] ([ProdId], [ProdDesc], [ProdPrice], [Version]) 
VALUES (N'1001', N'Toys', CAST(2500 AS Decimal(18, 0)), 2),
       (N'1002', N'Books', CAST(1800 AS Decimal(18, 0)), 3)
GO

INSERT INTO [dbo].[Product_Master] ([ProdId], [ProdDesc], [ProdPrice], [Version]) 
VALUES (N'1001', N'Toys', CAST(2500 AS Decimal(18, 0)), 2),
       (N'1001', N'Toys', CAST(2000 AS Decimal(18, 0)), 1),
       (N'1002', N'Perfumes', CAST(1500 AS Decimal(18, 0)), 1),
       (N'1002', N'Perfumes', CAST(1500 AS Decimal(18, 0)), 2),
       (N'1002', N'Perfumes', CAST(1800 AS Decimal(18, 0)), 3)
GO

Image was attached for better understanding.

enter image description here

Use a CTE to select the records you want then `UPDATE` the CTE. — Shawn, Jul 26 '18 at 21:46

score 1 · Answer 1 · answered Jul 26 '18 at 22:02

based on the image you have shared, simple inner join can be used between both tables to update price in master table.

Update PMT
SET PMT.ProdPrice = PT.ProdPrice
From prod_master_table PMT
INNER JOIN Product_Table PT On PT.ProdId = PMT.ProdId and PMT.Version = PT.Version

Shawn · Answer 2 · 2018-07-27T14:46:52.537

I'm not sure if you intended to include Version in your Product table. If you want to match them, it's just a simple UPDATE based on an INNER JOIN.

Your question seemed to indicate that you wanted to update the most recent record with what's in the Product table. If that's the case, then use a CTE and a MERGE.

; WITH cte (ProdID, ProdDesc, ProdPrice, Version) AS (
  SELECT ProdID, ProdDesc, ProdPrice, Version
  FROM (
    SELECT ProdID, ProdDesc, ProdPrice, Version
       , ROW_NUMBER() OVER (PARTITION BY ProdID ORDER BY Version DESC) AS rn 
    FROM Product_Master
  )s1
  WHERE rn= 1
)
MERGE INTO cte AS tgt
USING Product AS src
ON tgt.ProdID = src.ProdID
WHEN MATCHED
THEN
  UPDATE
  SET tgt.ProdPrice = src.ProdPrice
    , tgt.Version = src.Version
;

========================================

EDIT: OP Edit was supposed to be a Comment.

Thanks a lot for your effort and support Shawn. Few queries

Q1) Is it possible to create a stored procedure with your CTE ?

A1) I don't see why it wouldn't be. That code would probably be ideal for a stored procedure.

Q2) Is it possible to extend your CTE to update multiple tables?

For example Product table having some additional column named Supplier Name, is it possible to update those columns in other tables like SupplierDetails along with updating Product_Master?_

enter image description here

A2) I can't see your image where I am, so I don't know if it adds any additional details to your original post. Depending on what you are trying to do, you may be able to push it through the same sproc or call a different one to make the modifications. I'll try to look at the image later.

@raajesh For your additional questions, I'm not sure of what you're trying to do. You probably don't want to update two unrelated tables from the original query. You might need to create a new question with the entire workflow of what you're trying to do. The MERGE can accomplish a lot of what you're looking for, but it may not be the appropriate functionality to accomplish your ultimate goal. — Shawn, Jul 27 '18 at 15:22
Thanks Shawn, Q1 I can manage. Regarding Q2. changed the requirement that's why I can't define it earlier. i will give you the script for tables soon. — raajesh, Jul 27 '18 at 22:50
Shawn I've opened a new question on below link. please help if you can https://stackoverflow.com/questions/51568137/sql-server-update-data-into-multiple-tables-from-master-table-based-on-version — raajesh, Jul 28 '18 at 09:24

SQL Server Update Latest Records Only in a Group

2 Answers2