CODEWITHSUNDEEP
sqlsql-servert-sql
Kevin Hooper
Kevin Hooper

Reputation: 13

SQL SERVER How to update duplicate records with a SET UPDATE instead of using a CURSOR

What is the best practice for iterating through a result set that has duplicate ID's in SQL Server? I am currently using a CURSOR to loop over 40000 records in a stored procedure. Execution time for the stored procedure can be up to an hour in my lower environments.

Here is an example of the data from my SELECT.

ID CodeValue
123456 HI
123456 OHI
123456 OI
123456 SLI
123456 VI
123456 MD
987654 OI
876543 MD

I would like to use a SET Operation to update my table with every row brought back in the SELECT but the SET excludes the duplicates.
So in other words, I need to loop through and UPDATE my table with all the records. The records contain duplicate ID's but each row is different based upon the 2nd CodeValue column.

Below when using the SET operation my code only picks up the first of the 6 records with the same ID

ID CodeValue
123456 HI
987654 OI
876543 MD

How can I write my SET operation to select all the records? Here is the SELECT example for the data set.

SELECT ID, CodeValue into #tempTable
FROM ExtractA
UNION
SELECT ID, CodeValue
FROM ExtractB 
WHERE Indicator = 0

Here is the SET UPDATE example:

    UPDATE ta
        ,ta.[HI] =
            CASE
                WHEN  t.CodeValue = 'HI' THEN 1 
                else ta.[HI]
            END 
        ,ta.[MD] =
            CASE
                WHEN t.CodeValue = 'MD' THEN 1 
                ELSE ta.[MD]
            END
        ,ta.[OHI] =
            CASE
                WHEN t.CodeValue = 'OHI'  THEN 1 
                ELSE ta.[OHI]
            END 
        ,ta.[OI] =
            CASE
                WHEN t.CodeValue = 'OI' THEN 1 
                ELSE ta.[OI]
            END     
        ,ta.[SLI] =
            CASE
                WHEN t.CodeValue = 'SLI'  THEN 1 
                ELSE ta.[SLI]
            END
        ,ta.[VI] =
            CASE
                WHEN t.CodeValue = 'VI'  THEN 1                     
                ELSE  ta.[VI]
            END         
    FROM [dbo].[tableA] ta 
    JOIN #tempTable t 
        on ta.ID = t.ID
    WHERE ta.FiscalYear = @piFiscalYear

Upvotes: 1

Views: 163

Answers (1)

Joe S
Joe S

Reputation: 346

Here is an attempt at producing the results you are looking for. This solution assumes that you have a finite set of CodeValue entries. If they are variable or numerous, this may not work for you.

The #pivotTable temp table is what @oglester was referring to. It transposes the data so instead of having multiple records per ID, it becomes one record per ID.

Below the code is the #tableA output before and after the update.

DECLARE @piFiscalYear SMALLINT = 2022;

DROP TABLE IF EXISTS #tempTable;
CREATE TABLE #tempTable
(
    ID INT NOT NULL
    , CodeValue VARCHAR(3) NOT NULL
);

INSERT INTO #tempTable (ID, CodeValue) VALUES
    (123456, 'HI'),
    (123456, 'OHI'),
    (123456, 'OI'),
    (123456, 'SLI'),
    (123456, 'VI'),
    (123456, 'MD'),
    (987654, 'OI'),
    (876543, 'MD');

DROP TABLE IF EXISTS #tableA;
CREATE TABLE #tableA
(
    ID INT NOT NULL
    , FiscalYear SMALLINT NOT NULL
    , [HI] VARCHAR(3) NULL
    , [OHI] VARCHAR(3) NULL
    , [OI] VARCHAR(3) NULL
    , [SLI] VARCHAR(3) NULL
    , [VI] VARCHAR(3) NULL
    , [MD] VARCHAR(3) NULL
    , AnotherColumn VARCHAR(15) NULL
);

INSERT INTO #tableA
    (
        ID
        , FiscalYear
        , [HI], [OHI], [OI], [SLI], [VI], [MD]
        , AnotherColumn
    )
VALUES
    (123456, 2022, NULL, NULL, NULL, NULL, NULL, NULL, 'Some data'),
    (987654, 2022, NULL, NULL, NULL, NULL, NULL, NULL, 'More data'),
    (876543, 2022, NULL, NULL, NULL, NULL, NULL, NULL, 'So much data'),
    (123456, 2021, NULL, NULL, NULL, NULL, NULL, NULL, 'Old data'),
    (987654, 2000, NULL, NULL, NULL, NULL, NULL, NULL, 'Really old data'),
    (876543, 1901, NULL, NULL, NULL, NULL, NULL, NULL, 'Just for fun');

DROP TABLE IF EXISTS #pivotTable;
SELECT
    ID, [HI], [OHI], [OI], [SLI], [VI], [MD]
INTO #pivotTable
FROM
    (SELECT ID, CodeValue FROM #tempTable) t
    PIVOT (COUNT(CodeValue) FOR CodeValue IN ([HI],[OHI],[OI],[SLI],[VI],[MD])
    ) AS pvt;

SELECT * FROM #tableA;

UPDATE ta
    SET [HI] = pvt.[HI],
        [OHI] = pvt.[OHI],
        [OI] = pvt.[OI],
        [SLI] = pvt.[SLI],
        [VI] = pvt.[VI],
        [MD] = pvt.[MD]
FROM
    #tableA ta
    INNER JOIN #pivotTable pvt
        ON ta.ID = pvt.ID
WHERE
    ta.FiscalYear = @piFiscalYear;

SELECT * FROM #tableA;

Before the update:

ID FiscalYear HI OHI OI SLI VI MD AnotherColumn
123456 2022 NULL NULL NULL NULL NULL NULL Some data
987654 2022 NULL NULL NULL NULL NULL NULL More data
876543 2022 NULL NULL NULL NULL NULL NULL So much data
123456 2021 NULL NULL NULL NULL NULL NULL Old data
987654 2000 NULL NULL NULL NULL NULL NULL Really old data
876543 1901 NULL NULL NULL NULL NULL NULL Just for fun

After the update:

ID FiscalYear HI OHI OI SLI VI MD AnotherColumn
123456 2022 1 1 1 1 1 1 Some data
987654 2022 0 0 1 0 0 0 More data
876543 2022 0 0 0 0 0 1 So much data
123456 2021 NULL NULL NULL NULL NULL NULL Old data
987654 2000 NULL NULL NULL NULL NULL NULL Really old data
876543 1901 NULL NULL NULL NULL NULL NULL Just for fun

Upvotes: 1

Related Questions