CODEWITHSUNDEEP
sqlsql-server
dpak
dpak

Reputation: 55

Recursive Update Statement

I need to create a recursive update statement that updates from another table so for ex..

    Table1
    (
       IdNumberGeneratedFromAService INT NOT NULL,
       CodeName NVARCHAR(MAX)
    )

    Table2 
    (
       Table2Id Auto_Increment,
       Name NVARCHAR(MAX),
       IdNumberThatComesFromTabl1,
       CodeNameForTable1ToMatch
    )

the issue is CodeNameForTable1ToMatch is not unique so if Table1 has 2 idnumber for the same code and there are two rows in Table2 with the same CodeName I want to update the rows in table2 in sequence so first row gets the first idnumber and second row gets the second id number.

Also want to do it without cursor....

SAMPLE DATA

Table1
idNumber            Code
C145-6678-90        Code1
C145-6678-91        Code1
C145-6678-92        Code1
C145-6678-93        Code1   
C145-6678-94        Code1


Table 2
AutoIncrementIdNumber       Code        IdNumber
1               Code1       {NULL}
2               Code1       {NULL}
3               Code1       {NULL}
4               Code1       {NULL}
5               Code1       {NULL}


C145-6678-90 needs to got 1
C145-6678-91 needs to got 2
C145-6678-92 needs to got 3
C145-6678-93 needs to got 4
C145-6678-94 needs to got 5

in one update statement

Upvotes: 3

Views: 1801

Answers (3)

Ben Thul
Ben Thul

Reputation: 32717

A riff on Tarwn's solution:

with cte1 as (
   select code, row_number() over (partition by code order by idNumber) as [rn]
   from table1
), cte2 as (
   select code, row_number() over (partition by code order by AutoIncrementIdNumber) as [rn]
   from table2 
)
update cte2
set idNumber = cte1.idNumber
from cte2
inner join cte1
   on cte2.code = cte1.code
   and cte2.rn = cte1.rn

I only present this because people are often amazed that you can update a common table expression.

Upvotes: 1

Tarwn
Tarwn

Reputation: 1030

Using the ROW_NUMBER windowing function on each of the tables, partitioned by the code, you can number each of the rows that have a code in common, then combine the results of that on each query to match rows based on the code and the numbered instance of that code. So the first Code A in Table 1 would matched the first Code A in table 2, and etc.

Sample code showing this (SQL 2005 or higher):

-- Sample code prep
CREATE TABLE #Table1
(
   IdNumberGeneratedFromAService INT NOT NULL,
   CodeName NVARCHAR(MAX)
);

CREATE TABLE #Table2 
(
   Table2Id INT NOT NULL IDENTITY(1,1),
   Name NVARCHAR(MAX),
   IdNumberThatComesFromTabl1 INT NULL,
   CodeNameForTable1ToMatch  NVARCHAR(MAX)
);


INSERT INTO #Table1(IdNumberGeneratedFromAService, CodeName)
VALUES(100,'Code A'),(150,'Code A'),(200,'Code B'),(250,'Code A'),(300,'Code C'),(400,'Nonexistent');

INSERT INTO #Table2(Name, IdNumberThatComesFromTabl1, CodeNameForTable1ToMatch)
VALUES('A1-100',0,'Code A'),('A2-150',0,'Code A'),('A3-250',0,'Code A'),('B1-200',0,'Code B'),('C1-300',0,'Code C'),('No Id For Me',0,'Code No Id :(');

-- Sample select statement that shows the row numbers
--SELECT * 
--FROM
--  (SELECT *, ROW_NUMBER() OVER (Partition By IT2.CodeNameForTable1ToMatch Order By IT2.Table2Id) as RowNum
--      FROM #Table2 IT2) T2
--  INNER JOIN
--  (SELECT *, ROW_NUMBER() OVER (Partition By IT1.CodeName Order By IT1.IdNumberGeneratedFromAService) as RowNum
--      FROM #Table1 IT1) T1
--      ON T1.CodeName = T2.CodeNameForTable1ToMatch AND T1.RowNum = T2.RowNum;

-- Table 2 Before
SELECT * FROM #Table2;

-- Actual update statement
UPDATE #Table2
SET IdNumberThatComesFromTabl1 = T1.IdNumberGeneratedFromAService
FROM #Table2 AT2
INNER JOIN
    (SELECT *, ROW_NUMBER() OVER (Partition By IT2.CodeNameForTable1ToMatch Order By IT2.IdNumberThatComesFromTabl1) as RowNum
        FROM #Table2 IT2) T2
    ON T2.Table2Id = AT2.Table2Id
INNER JOIN 
    (SELECT *, ROW_NUMBER() OVER (Partition By IT1.CodeName Order By IT1.IdNumberGeneratedFromAService) as RowNum
        FROM #Table1 IT1) T1
        ON T1.CodeName = T2.CodeNameForTable1ToMatch AND T1.RowNum = T2.RowNum;

-- Table 2 after
SELECT * FROM #Table2;

-- Cleanup
DROP TABLE #Table1;
DROP TABLE #Table2;

I turned your two sample tables into temp tables and added 3 records for 'Code A', a record for 'Code B', and a record for 'Code C'. The codes in table1 are numbered based on the order of the table 1 ID, the codes in Table 2 are ordered by the auto-incrementing Table 2 id. I also included a record in each table that wouldn't have a match in the other. I tried to make the code's descriptive so it would be easier to see that a correct match has occurred (they order for table 2 is important since it has an auto incrementing id)

The commented out sample select is there to help understand how the select works before I join it into the UPDATE statement.

So we can see before the update Table 2 is all 0's, then we update the values in table 2 where the unique table 2 id matches the unique table 2 id from our nicely numbered and matched join, then we select from table 2 again to see the results.

Upvotes: 6

Craig
Craig

Reputation: 7076

This isn't possible without a cursor.

Upvotes: -3

Related Questions