September 12, 2018 at 6:21 am
IN MY BELOW sample RECORDS I have to update f_authorized=0 for the record which f_date_Stamp column contains maximum value.
All the columns having duplicate values except F_DATE_STAMP column.F_PRODUCT F_FORMAT F_SUBFORMAT F_LANGUAGE F_PLANT F_AUTHORIZED F_DATE_STAMP
000309 MTR WPR1 EN TEST -1 2013-07-11 14:18:24.000
000309 MTR WPR1 EN TEST -1 2013-07-10 14:18:22.000
sample output
F_PRODUCT F_FORMAT F_SUBFORMAT F_LANGUAGE F_PLANT F_AUTHORIZED F_DATE_STAMP
000309 MTR WPR1 EN TEST 0 2013-07-11 14:18:24.000
000309 MTR WPR1 EN TEST -1 2013-07-10 14:18:22.000
September 12, 2018 at 6:33 am
If you provide the sample data in a consumable format (ie, one which someone can cut & paste directly into SSMS), you'll soon get a coded solution (probably using a CTE and the ROW_NUMBER() function).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 12, 2018 at 6:35 am
Can try something like ROW_NUMBER.
ROW_NUMBER() OVER(PARTITION BY DuplicatedFields ORDER BY F_DATE_STAMP DESC) AS Row#
Or you can join the data ontop of each other with MAX().
Then learn if it's possible to do an UPDATE with a JOIN.
September 12, 2018 at 6:37 am
jkramprakash - Wednesday, September 12, 2018 6:21 AMIN MY BELOW sample RECORDS I have to update f_authorized=0 for the record which f_date_Stamp column contains maximum value.
All the columns having duplicate values except F_DATE_STAMP column.F_PRODUCT F_FORMAT F_SUBFORMAT F_LANGUAGE F_PLANT F_AUTHORIZED F_DATE_STAMP
000309 MTR WPR1 EN TEST -1 2013-07-11 14:18:24.000
000309 MTR WPR1 EN TEST -1 2013-07-10 14:18:22.000
sample output
F_PRODUCT F_FORMAT F_SUBFORMAT F_LANGUAGE F_PLANT F_AUTHORIZED F_DATE_STAMP
000309 MTR WPR1 EN TEST 0 2013-07-11 14:18:24.000
000309 MTR WPR1 EN TEST -1 2013-07-10 14:18:22.000
And how did you select the two rows that you are showing? That SELECT statement can have a ROW_NUMBER() function added to it that uses OVER (ORDER BY F_DATE_STAMP DESC) as a CTE, and the UPDATE references the CTE and applies a WHERE clause that specifies the column that is the row number = 1.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 12, 2018 at 10:01 am
Here's one way to do it
-- Use a common table expression to find the max values for records and records that are duplicated
-- This won't work if there are duplicate timestamps for the max record.
WITH CTE AS (SELECT F_PRODUCT, F_FORMAT, F_SUBFORMAT, F_LANGUAGE, F_PLANT, F_AUTHORIZED, MAX(F_DATE_STAMP) AS STAMP
FROM yourTable
GROUP BY F_PRODUCT, F_FORMAT, F_SUBFORMAT, F_LANGUAGE, F_PLANT, F_AUTHORIZED
HAVING COUNT(F_PRODUCT) > 1)
UPDATE T SET F_AUTHORIZED = 0
FROM CTE INNER JOIN yourTable T ON
CTE.F_PRODUCT = T.F_PRODUCT
AND CTE.F_FORMAT = T.F_FORMAT
AND CTE.F_SUBFORMAT = T.F_SUBFORMAT
AND CTE.F_LANGUAGE = T.F_LANGUAGE
AND CTE.F_PLANT = T.F_PLANT
AND CTE.STAMP = T.F_DATE_STAMP
September 12, 2018 at 12:33 pm
souLTower - Wednesday, September 12, 2018 10:01 AMHere's one way to do it
-- Use a common table expression to find the max values for records and records that are duplicated
-- This won't work if there are duplicate timestamps for the max record.
WITH CTE AS (SELECT F_PRODUCT, F_FORMAT, F_SUBFORMAT, F_LANGUAGE, F_PLANT, F_AUTHORIZED, MAX(F_DATE_STAMP) AS STAMP
FROM yourTable
GROUP BY F_PRODUCT, F_FORMAT, F_SUBFORMAT, F_LANGUAGE, F_PLANT, F_AUTHORIZED
HAVING COUNT(F_PRODUCT) > 1)
UPDATE T SET F_AUTHORIZED = 0
FROM CTE INNER JOIN yourTable T ON
CTE.F_PRODUCT = T.F_PRODUCT
AND CTE.F_FORMAT = T.F_FORMAT
AND CTE.F_SUBFORMAT = T.F_SUBFORMAT
AND CTE.F_LANGUAGE = T.F_LANGUAGE
AND CTE.F_PLANT = T.F_PLANT
AND CTE.STAMP = T.F_DATE_STAMP
This requires you to read the table twice: once to figure out the max values, and once to update the table. The method that Steve alluded to only requires one read of the table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 13, 2018 at 6:12 am
I agree with you J. Drew Allen. It's not the MOST efficient way to do it but it demonstrates the mechanics of how to do it. The approach you mention is more advanced and I felt that posting it hides the details. I was trying to use a teaching approach.
September 13, 2018 at 6:52 am
souLTower - Thursday, September 13, 2018 6:12 AMI agree with you J. Drew Allen. It's not the MOST efficient way to do it but it demonstrates the mechanics of how to do it. The approach you mention is more advanced and I felt that posting it hides the details. I was trying to use a teaching approach.
Thank you.This table contains ten thousand records only.so i will use this method.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply