CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2sql-update
raajesh
raajesh

Reputation: 115

SQL Server Update Latest Records Only in a Group

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

Upvotes: 2

Views: 41

Answers (2)

Shawn
Shawn

Reputation: 4786

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.

Upvotes: 0

Vinit
Vinit

Reputation: 2607

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

Upvotes: 1

Related Questions