Results 1 to 5 of 5
  1. #1
    Join Date
    May 2007
    Posts
    2

    Unanswered: Update Table with SEQUENTIAL # based on criteria

    ***** SQL Server 2005 ********

    I have a table that needs to be updated with a sequential number based on criteria.

    I am trying to update the SeqID and LinkSeqID with the same sequential number if the ProductID and StoreID are in the same group. For instance the 1st three rows below are in the same group 752534 and 4, therefore the SeqID and LinkSeqID should be 1,2,3 and restart at 1 once the grouping of ProductID and StoreID changes. Please look at the examples below.


    SALES Table as IS:
    ======================================
    ProductID StoreID DBRowID SeqID LinkSeqID
    ======================================
    752534 4 1
    752534 4 2
    752534 4 3
    896784 2 4
    896784 2 5
    896784 4 6
    898874 2 7
    898968 2 8


    This is what the table should look like after the update in complete.

    SALES after UPDATE:
    ======================================
    ProductID StoreID DBRowID SeqID LinkSeqID
    ======================================
    752534 4 1 1 1
    752534 4 2 2 2
    752534 4 3 3 3
    896784 2 4 1 1
    896784 2 5 2 2
    896784 4 6 1 1
    898874 2 7 1 1
    898968 2 8 1 1



    Can anyone HELP me please?
    Last edited by Crystie; 05-09-07 at 17:40.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is air code (if you post DDL and DML then we can write real, tested code )

    Code:
    SELECT  ProductID
            , StoreID
            , ROW_NUMBER() OVER     (PARTITION BY   ProductID
                                                    , StoreID
                                    ORDER BY        ProductID
                                                    , StoreID) AS row_num
    FROM dbo.Sales
    That is the hard part done. I presume you are ok with the actual update?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2007
    Posts
    2

    Thanks! Question

    Ok, thanks for the info it was helpful. But the seqid is not correct. The SEQID is suppose to be in order 1,2,3 etc.. and reset to 1 when the ProductID and StoreID change. After running your SQL script this is the result.

    ***SeqID is not correct****

    ProductID StoreID DBRowID SeqID LinkSeqID
    ===================================
    752534 4 1 1 NULL
    752534 4 2 3 NULL
    752534 4 3 1 NULL
    896784 2 4 1 NULL
    896784 2 5 2 NULL
    896784 4 6 1 NULL
    898874 2 7 1 NULL
    898968 2 8 1 NULL

    Your SQL
    ============
    use tempdb
    go

    create table #sales
    (
    productid int
    , storeid int
    , dbrowid int
    , seqid int
    , linkseqid int
    )

    insert into #sales (productid, storeid, dbrowid)
    select 752534, 4, 1
    union all
    select 752534, 4, 2
    union all
    select 752534, 4, 3
    union all
    select 896784, 2, 4
    union all
    select 896784, 2, 5
    union all
    select 896784, 4, 6
    union all
    select 898874, 2, 7
    union all
    select 898968, 2, 8
    go

    with rns
    as
    (
    select productid
    , storeid
    , row_number() over (partition by productid
    , storeid
    order by productid
    , storeid) as row_num
    from #sales
    )
    update s
    set seqid = row_num
    from #sales as s
    inner join
    rns
    on rns.productid = s.productid
    and rns.storeid = s.storeid

    select *
    from #sales

    drop table #sales

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmm - that seems to be something to do with the CTE. If you run this it works fine (note the code has not actually changed):

    Code:
     
    use tempdb
    go
     
    create table #sales 
    (
    productid int
    , storeid int
    , dbrowid int
    , seqid int
    , linkseqid int
    )
     
    insert into #sales (productid, storeid, dbrowid)
    select 752534, 4, 1
    union all 
    select 752534, 4, 2
    union all 
    select 752534, 4, 3
    union all 
    select 896784, 2, 4
    union all 
    select 896784, 2, 5
    union all 
    select 896784, 4, 6
    union all 
    select 898874, 2, 7
    union all 
    select 898968, 2, 8
    go
     
    select productid
    , storeid
    , ROW_NUMBER() over (partition by productid
    , storeid
    order by productid
    , storeid) as row_num
    from #sales
     
    drop table #sales
    I guess you will need to pop into a temp table then and update from that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •