SQL Server - Avoid cursor while serial update

  • sbob

    SSC Veteran

    Points: 273

    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

     

    The results should be exactly the following:

    correct_results

    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

  • Phil Parkin

    SSC Guru

    Points: 244004

    Yuck! I started on this before spotting the need for some sort of recursion, to make the Person A4 bit work.

    If I had more time, I think that I would first build a query to create an 'enhanced' #transfers table. This version would contain straight mappings (Person One to Person A4 etc), making the rest of exercise trivial.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • jonathan.crawford

    SSCertifiable

    Points: 6412

    What are you really trying to do here? Why overwrite, when you could just capture the history by adding a date of transfer or something and have the whole history?

     

    -------------------------------------------------------------------------------------------------------------------------------------
    Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

  • Mark Cowne

    One Orange Chip

    Points: 26734

    Here's a recursive CTE solution

    WITH Recur AS (
     SELECT Person_FROM AS Person_FROM_Start, Person_FROM, Person_To, Kind, Pctg_New, Eff_Date
     FROM #transfers
     UNION ALL
     SELECT ts.Person_FROM_Start, te.Person_FROM, te.Person_To, te.Kind, te.Pctg_New, te.Eff_Date
     FROM Recur ts
     INNER JOIN #transfers te ON ts.Person_To = te.Person_FROM AND ts.Kind = te.Kind AND te.Eff_Date > ts.Eff_Date
    ),
    Results AS (
     SELECT Person_FROM_Start,Person_To,Kind,Pctg_New,Eff_Date,
     ROW_NUMBER() OVER(PARTITION BY Person_FROM_Start,Kind ORDER BY Eff_Date DESC) AS rn
     FROM Recur
    )
    SELECT ISNULL(r.Person_To,h.Person) AS Person, h.Kind, ISNULL(r.Pctg_New,h.Pctg) AS Pctg
    FROM #holders h
    LEFT OUTER JOIN Results r ON r.Person_FROM_Start = h.Person AND r.Kind = h.Kind AND r.rn = 1;

     

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • sbob

    SSC Veteran

    Points: 273

    Super! Thanks a lot

  • Jonathan AC Roberts

    SSCoach

    Points: 17166

    No need for a cte:

    update h
    set h.Person = t.Person_To,
    h.Pctg = t.Pctg_New
    from #holders h
    inner join #transfers t
    on t.Kind = h.Kind
    and t.Person_FROM = h.Person
  • Phil Parkin

    SSC Guru

    Points: 244004

    Jonathan AC Roberts wrote:

    No need for a cte:

    update h
    set h.Person = t.Person_To,
    h.Pctg = t.Pctg_New
    from #holders h
    inner join #transfers t
    on t.Kind = h.Kind
    and t.Person_FROM = h.Person

    This does not correctly update Person One.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • sbob

    SSC Veteran

    Points: 273

    Hi @jonathan-2 AC Roberts

    Thanks for your answer

    Applied this update the results will be:

    Person                Kind         Pctg

    Person A            Kind 1      70

    Person B            Kind 1      30

    Person Three    Kind 1      NULL

    Person Four      Kind 1      NULL

    Person One       Kind 2     100

    which are incorrect

  • Jonathan AC Roberts

    SSCoach

    Points: 17166

    Phil Parkin wrote:

    Jonathan AC Roberts wrote:

    No need for a cte:

    update h
    set h.Person = t.Person_To,
    h.Pctg = t.Pctg_New
    from #holders h
    inner join #transfers t
    on t.Kind = h.Kind
    and t.Person_FROM = h.Person

    This does not correctly update Person One.

    Ah, yes! I see why the recursive CTE was suggested now.

  • scdecade

    SSChasing Mays

    Points: 655

    Nicely done Mark Cowne.  My attempt avoids the inequality on date (which makes me nervous) but is otherwise the same.


    with
    t_rn_cte(person_from, person_to, kind, pctg_new, eff_date, row_num) as (
    select
    t.*, row_number() over(partition by person_from, kind order by eff_date asc) row_num
    from
    #transfers t),
    t_cte(orig_person_from, person_from, person_to, kind, pctg_new, eff_date, h_level) as (
    select
    t.person_from, t.person_from, t.person_to, t.kind, t.pctg_new, t.eff_date, 0
    from
    t_rn_cte t
    where
    t.row_num=1
    union all
    select
    hc.orig_person_from, t.person_from, t.person_to, t.kind, t.pctg_new, t.eff_date, hc.h_level+1
    from
    t_rn_cte t
    join
    t_cte hc on t.person_from=hc.person_to
    and t.kind=hc.kind
    and t.row_num=1),
    t_level_rn_cte(orig_person_from, person_from, person_to, kind, pctg_new, eff_date, h_level, row_num) as (
    select
    tc.*, row_number() over(partition by orig_person_from, kind order by h_level desc) row_num
    from
    t_cte tc)
    select
    coalesce(tc.person_to, h.person) person,
    h.kind,
    coalesce(tc.pctg_new, h.Pctg) pctg
    from
    #holders h
    left join
    t_level_rn_cte tc on h.person=tc.orig_person_from
    and h.kind=tc.kind
    and tc.row_num=1
    order by
    h.kind,
    coalesce(tc.person_to, h.person) asc;
    go

     

    • This reply was modified 1 month ago by  scdecade.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply