I have table as below:

            Input table:
            RequestNumber            TrackName         Date
            -----------------------------------------------------------
            02209                    Middle         2017-05-28 00:00:00
            0263                     Middle         2017-06-29 00:00:00
            0633                     Middle         2017-09-10 00:00:00
            0762                     Back           2017-06-23 00:00:00
            0762                     Front          2017-06-23 00:00:00
            0762                     Middle         2017-06-23 00:00:00
            01839                    Middle         2017-03-09 00:00:00

I need to check, if "RequestNumber" and "Date" is same for "TrackName". if multiple reacords of "RequestNumber" and "Date" matches then I should update the "TrackName" column as "All three" as below output example (in this example there are 3 records matching)

            Output table:
            RequestNumber            TrackName         Date
            -----------------------------------------------------------
            02209                    Middle         2017-05-28 00:00:00
            0263                     Middle         2017-06-29 00:00:00
            0633                     Middle         2017-09-10 00:00:00
            0762                     All three      2017-06-23 00:00:00
            01839                    Middle         2017-03-09 00:00:00

To get the above desigred output this is the SQL I have tried. Howveer, its updating all TrackName to All three.

            UPDATE a 
            SET a.[TrackName] = 'All three'
            FROM Table1 as a
            INNER JOIN 
            (SELECT [RequestNumber], row_number() OVER (ORDER BY [RequestNumber] DESC) as rowNumber
            FROM Table1 ) drRowNumbers ON drRowNumbers.[RequestNumber] = a.[RequestNumber] and drRowNumbers.[Date] = a.[Date]

Hope I have explained this correctly. What am I doing worng? Any query to fix this please?

Note: The records are coming dynamically so can not be hardcoded(if any).

Thanks.

4 Answers

1
mkRabbani On Best Solutions

Try This. This will return "All 3" instead of "All Three"-

SELECT RequestNumber,
CASE 
    WHEN COUNT(*) = 1 THEN MAX(TrackName) 
    ELSE 'All ' + CAST( COUNT(*) AS VARCHAR) 
END TrackName,
Date
FROM your_table
GROUP BY RequestNumber,Date
0
Yogesh Sharma On

I think you want SELECT statement :

SELECT t1.RequestNumber, (CASE WHEN COUNT(DISTINCT t1.TrackName) = t2.TrackNo 
                               THEN CONCAT('All ', t2.TrackNo)  
                               ELSE MIN(t1.TrackName) 
                          END) AS TrackName, t1.Date
FROM table1 t1 CROSS JOIN
     (SELECT COUNT(DISTINCT TrackName) AS TrackNo FROM table1) AS t2
GROUP BY t1.RequestNumber, t1.Date;
0
Zohar Peled On

This is not an just an update, it's also a delete.
This means you must have a two step process - one to update relevant records and the next to delete the duplicates created by the first step.

This can be done using a couple of common table expressions that use window functions such as count() over() and row_number() over(), when both steps are joined inside a transaction.

First, create and populate sample table (Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    RequestNumber int,
    TrackName varchar(10),
    [Date] datetime2
);

INSERT INTO @T (RequestNumber, TrackName, Date) VALUES
(02209, 'Middle', '2017-05-28 00:00:00'),
(0263, 'Middle', '2017-06-29 00:00:00'),
(0633, 'Middle', '2017-09-10 00:00:00'),
(0762, 'Back', '2017-06-23 00:00:00'),
(0762, 'Front', '2017-06-23 00:00:00'),
(0762, 'Middle', '2017-06-23 00:00:00'),
(01839, 'Middle', '2017-03-09 00:00:00');

Next, start a try block and a transaction:

BEGIN TRY
BEGIN TRANSACTION;

Then, identify and update the relevant records:

WITH CTE AS
(
    SELECT  RequestNumber, 
            TrackName, 
            Date,
            COUNT(TrackName) OVER(PARTITION BY RequestNumber, Date) As Cnt
    FROM @T
)

UPDATE CTE
SET TrackName = 'All Three'
WHERE Cnt = 3;

Next, delete the duplicates:

WITH CTE AS
(
    SELECT  RequestNumber, 
            TrackName, 
            Date,
            ROW_NUMBER() OVER(PARTITION BY RequestNumber, Date ORDER BY TrackName) As Rn
    FROM @T
)

DELETE 
FROM CTE 
WHERE Rn > 1;

Commit the transaction and close the try block:

COMMIT TRANSACTION;
END TRY

Use a catch block to rollback the transaction:

BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    -- you might want to print the result of ERROR_MESSAGE() here...
END CATCH

And finally, select to see the changes:

SELECT  RequestNumber, 
        TrackName, 
        Date
FROM @T

Results:

RequestNumber   TrackName   Date
2209            Middle      28.05.2017 00:00:00
263             Middle      29.06.2017 00:00:00
633             Middle      10.09.2017 00:00:00
762             All Three   23.06.2017 00:00:00
1839            Middle      09.03.2017 00:00:00

You can see a live demo on rextester (minus the transaction part which isn't allowed there and the try...catch which isn't relevant without the transaction anyway)

0
RegBes On

Give this a try

step 1 set one of the recs to All Three

update table1
set TrackName = 'All Three'
where requestnumber in (select requestnumber
from table1
group by requestnumber,[date]
having count(*) = 3)
and trackname = 'Front'

step 2 remove the data no longer required

delete table1
where requestnumber in (select requestnumber
from table1
group by requestnumber,[date]
having count(*) = 3)
and trackname <> 'All Three'