locked
SQL Server - Avoid cursor while serial update RRS feed

  • Question

  • How can I avoid use cursor to implement the following? I read that it can be done with CTE but I didn't get it working with the same result.

    In the example I am using two tables, the first one is the holders table containing a list of people and the transfers table where each transfer indicates a change at a specific record of the first table.

    Below you can see the code, which brings the right results:

    create table #holders(Person VARCHAR(50), Kind VARCHAR(50), Pctg FLOAT)
    create table #transfers(Person_FROM VARCHAR(50), Person_To VARCHAR(50), Kind VARCHAR(50), Pctg_New FLOAT, Eff_Date DATE)
    
    insert into #holders
    select 'Person One', 'Kind 1', 50 union all
    select 'Person Two', 'Kind 1', 50 union all
    select 'Person Three', 'Kind 1', NULL union all
    select 'Person Four', 'Kind 1', NULL union all
    select 'Person One', 'Kind 2', 100
    
    insert into #transfers
    select 'Person One', 'Person A', 'Kind 1', 70, '2019-12-31' union all
    select 'Person Two', 'Person B', 'Kind 1', 30, '2020-01-01' union all
    select 'Person A', 'Person A1', 'Kind 1', 70, '2020-01-02' union all
    select 'Person A', 'Person A2', 'Kind 1', 70, '2020-01-03' union all --Should Avoided
    select 'Person A2', 'Person A3', 'Kind 1', 70, '2020-01-04' union all --Should Avoided
    select 'Person A1', 'Person A4', 'Kind 1', 70, '2020-01-05'
    
    declare
    @Person_FROM varchar(50),
    @Person_To varchar(50),
    @Kind varchar(50),
    @Pctg_New float
    
    declare cur cursor for select Person_FROM, Person_To, Kind, Pctg_New from #transfers order by Eff_Date
    open cur
    fetch next from cur into @Person_FROM, @Person_To, @Kind, @Pctg_New
    while @@FETCH_STATUS = 0 begin
    update #holders set Person = @Person_To, Pctg = @Pctg_New where Person = @Person_FROM AND Kind = @Kind
    fetch next from cur into @Person_FROM, @Person_To, @Kind, @Pctg_New
    end
    close cur
    deallocate cur
    
    SELECT * FROM #holders
    
    drop table #holders
    drop table #transfers


    I think that the key is it is needed serialized update (order by Eff_Date) and some kind of recursive (The first line should update 3 times using this flow: "Person One" --> "Person A" --> "Person A1" --> "Person A4").

    Any help is welcome! Thanks in advance

    Friday, January 24, 2020 2:48 PM

All replies

  • Load data to the TABLE variable, i.e., @Table with RowID as IDENTITY (1, 1) column and then use WHILE loop to update the table. 

    A Fan of SSIS, SSRS and SSAS

    Friday, January 24, 2020 2:53 PM
  • Can you post the results you expect?

    A Fan of SSIS, SSRS and SSAS

    Friday, January 24, 2020 5:30 PM
  • All you really need to know if the last iteration and just a single update.

    IF OBJECT_ID('tempdb..#holders') IS NOT NULL
        DROP TABLE #holders;
    IF OBJECT_ID('tempdb..#transfers') IS NOT NULL
        DROP TABLE #transfers;
    
    create table #holders(Person VARCHAR(50), Kind VARCHAR(50), Pctg FLOAT)
    create table #transfers(Person_FROM VARCHAR(50), Person_To VARCHAR(50), Kind VARCHAR(50), Pctg_New FLOAT, Eff_Date DATE)
    
    insert into #holders
    select 'Person One', 'Kind 1', 50 union all
    select 'Person Two', 'Kind 1', 50 union all
    select 'Person Three', 'Kind 1', NULL union all
    select 'Person Four', 'Kind 1', NULL union all
    select 'Person One', 'Kind 2', 100
    
    insert into #transfers
    select 'Person One', 'Person A', 'Kind 1', 70, '2019-12-31' union all
    select 'Person Two', 'Person B', 'Kind 1', 30, '2020-01-01' union all
    select 'Person A', 'Person A1', 'Kind 1', 70, '2020-01-02' union all
    select 'Person A', 'Person A2', 'Kind 1', 70, '2020-01-03' union all --Should Avoided
    select 'Person A2', 'Person A3', 'Kind 1', 70, '2020-01-04' union all --Should Avoided
    select 'Person A1', 'Person A4', 'Kind 1', 70, '2020-01-05'
    
    
    
    ;WITH hpath as (
    SELECT t1.Person_FROM, t1.Person_To, t1.Kind, t1.Pctg_New, t1.Eff_Date, 1 as [level]
    FROM #holders h
        INNER JOIN #transfers t1 
        ON t1.Person_FROM = h.Person
        AND t1.Kind = h.Kind
    UNION ALL
    SELECT p.Person_FROM, t1.Person_To, p.Kind, t1.Pctg_New, t1.Eff_Date, p.level + 1 
    FROM hpath p
        INNER JOIN #transfers t1 
        ON t1.Person_FROM = p.Person_To
        AND t1.Kind = p.Kind
        AND t1.Eff_Date > p.Eff_Date
    ),
    lastver as (
    SELECT h.*
    FROM hpath h
        INNER JOIN (
    	   SELECT h.Person_FROM, h.Kind, MAX(h.Eff_Date) as [Eff_Date], MAX([level]) as [level]
    	   FROM hpath h
    	   GROUP BY h.Person_FROM, h.Kind
        ) maxpath
        ON maxpath.Person_FROM = h.Person_FROM
        AND maxpath.Kind = h.Kind
        AND maxpath.[level] = h.[level]
        AND maxpath.Eff_Date = h.Eff_Date
    ) 
    UPDATE h
        SET Person = l.Person_To,
    	   Pctg = l.Pctg_New
    FROM #holders h
        INNER JOIN lastver l
        ON l.Person_FROM = h.Person
        AND l.Kind = h.Kind
    ;
    
    SELECT *
    FROM #holders
    ORDER BY Person
    ;
    
    

    Friday, January 24, 2020 5:57 PM
  • You're still trying to write SQL as if you were working with 1960s, magnetic tape files. Since SQL, is a declarative, set oriented language, we hate local variables. This is the same thing as it would be in any other declarative language. You've also split attributes over multiple tables, which have bad names that don't follow ISO 11179 rules. If you want to track this history of something, then your table structures should have begin and end timestamps and the status that an attribute had during that time interval. There's also no such thing as a "person" in a properly designed schema; we want to know the role the person played in the data model (customer?, Staff?,, Employee?, Etc.)  . I'm also curious why you're using a float data type for something called a P CTG. I used to be a Fortran programmer decades ago, and I don't remember all of the rules for the math for floating-point but I do remember that it's incredibly complicated. Perhaps your scientific programming is more current than mine, but I probably would've used the decimal data type.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, January 24, 2020 7:50 PM
  • Hi,

    Would you like this one ?

    create table #holders(Person VARCHAR(50), Kind VARCHAR(50), Pctg FLOAT)
    create table #transfers(Person_FROM VARCHAR(50), Person_To VARCHAR(50), Kind VARCHAR(50), Pctg_New FLOAT, Eff_Date DATE)
    
    insert into #holders
    select 'Person One', 'Kind 1', 50 union all
    select 'Person Two', 'Kind 1', 50 union all
    select 'Person Three', 'Kind 1', NULL union all
    select 'Person Four', 'Kind 1', NULL union all
    select 'Person One', 'Kind 2', 100
    
    insert into #transfers
    select 'Person One', 'Person A', 'Kind 1', 70, '2019-12-31' union all
    select 'Person Two', 'Person B', 'Kind 1', 30, '2020-01-01' union all
    select 'Person A', 'Person A1', 'Kind 1', 70, '2020-01-02' union all
    select 'Person A', 'Person A2', 'Kind 1', 70, '2020-01-03' union all --Should Avoided
    select 'Person A2', 'Person A3', 'Kind 1', 70, '2020-01-04' union all --Should Avoided
    select 'Person A1', 'Person A4', 'Kind 1', 70, '2020-01-05'
    
    
    ;with cte1 as(
    select *, row_number ()over(order by Eff_Date) rn from(
    select null Person_FROM,*,null Eff_Date  from #holders 
    union all 
    select * from #transfers)t
    ),cte2 as (
      SELECT Person_FROM,Person, Kind, 0 AS TreeLevel, cast(Person as varchar(max))  AS Path,Pctg,Eff_Date
      FROM cte1 T1
      WHERE Person_FROM IS NULL
      UNION ALL
      SELECT T1.Person_FROM,T1.Person, T1.Kind, TreeLevel + 1, 
      CAST(Path + '->' + CAST(t1.Person as varchar(max)) AS varchar(max)) Path, T1.Pctg,T1.Eff_Date
      FROM cte1 T1
      INNER JOIN cte2 T2 ON T2.Person = T1.Person_FROM and T1.Kind=t2.Kind
    ),cte3 as(
    SELECT case when Person_FROM is null then Person else substring(Path, 1,CHARINDEX('->',Path)-1) end Person_From,
    case when Person_FROM is null then null else right(Path,CHARINDEX('>-',REVERSE(path))-1) end Person_To,
    Kind,Pctg,Eff_Date, TreeLevel
    FROM  cte2)
    ,cte4 as (
    select *, max(TreeLevel)over(partition by Person_FROM,Kind ) max_value ,
    row_number()over(partition by  Person_FROM,Kind,TreeLevel order by TreeLevel) rn,
    count(*)over(partition by  Person_FROM,Kind,TreeLevel order by TreeLevel) ct
    from cte3)
    select case when Person_To is null then Person_FROM else Person_To end Person,
    Kind,Pctg
    from cte4 where TreeLevel=max_value and rn=ct
    order by case when Person_To is null then Person_FROM else Person_To end
    /*
    Person             Kind     Pctg
    ------------------ -------- ----------------------
    Person A4          Kind 1   70
    Person B           Kind 1   30
    Person Four        Kind 1   NULL
    Person One         Kind 2   100
    Person Three       Kind 1   NULL
    */
    
    
    
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 27, 2020 7:12 AM