CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2008-r2duplicates
Santosh
Santosh

Reputation: 127

Update unique/duplicate Data Records using single Stored Procedure in SQL Server 2008 R3

I have a table FileInfo with below columns

**(SID int) Primary Key & AutoIncrement,
FileName varchar,
FilePath varchar,
fileExtn varchar,
CreatedDate smalldatetime,
Duplicate varchar,
LatestCopy varchar.**

the records in this are like shown below

1, 02-2511-510.pdf, C:\Desktop\02-2511-510.pdf, .pdf, 2000-10-12 00:00:00, NULL, NULL
2, 04-4648-500.doc, C:\Desktop\04-4648-500.doc, .doc, 2010-10-01 00:00:00, NULL, NULL
3, 02-2511-510.pdf, D:\Users\ers\02-2511-510.pdf, .pdf, 2000-11-13 00:00:00, NULL, NULL
4, 02-2511-510.pdf, E:\AGES\02-2511-510.pdf, .pdf, 2000-10-12 00:00:00, NULL, NULL

After running the stored procedure the table data should look like shown below

1, 02-2511-510.pdf, C:\Desktop\02-2511-510.pdf, .pdf, 2000-10-12 00:00:00, Y, N
2, 04-4648-500.doc, C:\Desktop\04-4648-500.doc, .doc, 2010-10-01 00:00:00, N, Y
3, 02-2511-510.pdf, D:\Users\ers\02-2511-510.pdf, .pdf, 2013-11-13 00:00:00, Y, Y
4, 02-2511-510.pdf, E:\AGES\02-2511-510.pdf, .pdf, 2000-10-12 00:00:00, Y, N
  1. Basically i need to find the duplicates based on FileName column.
  2. If the file occurs multiple times set Duplicate column as 'Y'
  3. Then find the LatestCopy of the file based on CreatedDate and set the LatestCopy value to Y

I am able to write a stored procedure to find the duplicates and update the column Duplicate as Y/N. I also want to include the marking of LatestCopy in the same stored procedure. How can i do that.

My apologies if i have made any mistake, as i am learning SQL now.

Regards

Upvotes: 1

Views: 767

Answers (1)

M.Ali
M.Ali

Reputation: 69554

Your Current Data

╔═════╦═════════════════╦══════════════════════════════╦══════════╦═════════════════════╦═══════════╦════════════╗
║ SID ║    FileName     ║           FilePath           ║ fileExtn ║     CreatedDate     ║ Duplicate ║ LatestCopy ║
╠═════╬═════════════════╬══════════════════════════════╬══════════╬═════════════════════╬═══════════╬════════════╣
║   1 ║ 02-2511-510.pdf ║ C:\Desktop\02-2511-510.pdf   ║ .pdf     ║ 2000-10-12 00:00:00 ║ NULL      ║ NULL       ║
║   2 ║ 04-4648-500.doc ║ C:\Desktop\04-4648-500.doc   ║ .doc     ║ 2010-10-01 00:00:00 ║ NULL      ║ NULL       ║
║   3 ║ 02-2511-510.pdf ║ D:\Users\ers\02-2511-510.pdf ║ .pdf     ║ 2000-11-13 00:00:00 ║ NULL      ║ NULL       ║
║   4 ║ 02-2511-510.pdf ║ E:\AGES\02-2511-510.pdf      ║ .pdf     ║ 2000-10-12 00:00:00 ║ NULL      ║ NULL       ║
╚═════╩═════════════════╩══════════════════════════════╩══════════╩═════════════════════╩═══════════╩════════════╝

Update Queries

1) Update Where Data is Duplicated 

    ;WITH Updateables
    AS
    (
      SELECT * , rn = ROW_NUMBER() OVER (PARTITION BY [FileName] ORDER BY [FileName])
DESC)
      FROM Table_Name
    )
    UPDATE Updateables 
    SET Duplicate = 'Y'
    WHERE [FileName] IN (SELECT [FileName] 
                    FROM Updateables WHERE rn > 1 )

2) Update to Y the latest Version

;WITH Updateables
AS
(
  SELECT * , rn2 = ROW_NUMBER() OVER (PARTITION BY [FileName] ORDER BY CreatedDate DESC)
  FROM Table_Name
)
UPDATE Updateables 
SET LatestCopy = 'Y'
WHERE [SID] IN (SELECT [SID]
                FROM Updateables WHERE rn2 = 1)

Result Set

╔═════╦═════════════════╦══════════════════════════════╦══════════╦═════════════════════╦═══════════╦════════════╗
║ SID ║    FileName     ║           FilePath           ║ fileExtn ║     CreatedDate     ║ Duplicate ║ LatestCopy ║
╠═════╬═════════════════╬══════════════════════════════╬══════════╬═════════════════════╬═══════════╬════════════╣
║   1 ║ 02-2511-510.pdf ║ C:\Desktop\02-2511-510.pdf   ║ .pdf     ║ 2000-10-12 00:00:00 ║ Y         ║ NULL       ║
║   2 ║ 04-4648-500.doc ║ C:\Desktop\04-4648-500.doc   ║ .doc     ║ 2010-10-01 00:00:00 ║ NULL      ║ Y          ║
║   3 ║ 02-2511-510.pdf ║ D:\Users\ers\02-2511-510.pdf ║ .pdf     ║ 2000-11-13 00:00:00 ║ Y         ║ Y          ║
║   4 ║ 02-2511-510.pdf ║ E:\AGES\02-2511-510.pdf      ║ .pdf     ║ 2000-10-12 00:00:00 ║ Y         ║ NULL       ║
╚═════╩═════════════════╩══════════════════════════════╩══════════╩═════════════════════╩═══════════╩════════════╝

SQLFIDDLE

Upvotes: 2

Related Questions