Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2005
    Posts
    22

    Question Unanswered: Sequential number UPDATE query

    I have a table called OrderDetails with the fields OrderID (existing field) and OrderLine (a new filed).

    Below is a snippet of data.

    OrderID OrderLine
    -------------------------
    10248 NULL
    10248 NULL
    10248 NULL
    10249 NULL
    10249 NULL
    10250 NULL
    10250 NULL
    10250 NULL
    10250 NULL
    10250 NULL
    10251 NULL
    10251 NULL
    10251 NULL

    What I'm trying to do is UPDATE the OrderLine with a sequential number for each (same) OrderID. See below for an illustration.

    OrderID OrderLine
    -------------------------
    10248 1
    10248 2
    10248 3
    10249 1
    10249 2
    10250 1
    10250 2
    10250 3
    10250 4
    10250 5
    10251 1
    10251 2
    10251 3

    Can someone be kind enough to help me out - preferably with code - with my problem?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    DECLARE @t AS  TABLE
        (OrderID INT, OrderLine INT)
    
    INSERT @t (OrderID)
    SELECT 10248 UNION ALL SELECT
    10248 UNION ALL SELECT
    10248 UNION ALL SELECT
    10249 UNION ALL SELECT
    10249 UNION ALL SELECT
    10250 UNION ALL SELECT
    10250 UNION ALL SELECT
    10250 UNION ALL SELECT
    10250 UNION ALL SELECT
    10250 UNION ALL SELECT
    10251 UNION ALL SELECT
    10251 UNION ALL SELECT
    10251
    
    SELECT  OrderID
          , arbitary_increment  = ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY (SELECT 1))
    FROM    @t
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2005
    Posts
    22
    Thanks for the reply.

    But as I said that was just a snippet. there are actually 2000+ records to update.

    So would i still be able to use the same code?

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes. You'd need to join it back to your table though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2005
    Posts
    22
    UUmmmmmm, any chance of the code....................ppllleeeaaaaasssee?!

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I ignored something in your problem and that was a mistake.

    In order to do this you need another column of data that differentiates each row. What you have posted has no key and implies that you believe that the order of rows has meaning. In a relational database table there should always be a key and there is no meaning to the order of rows.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2005
    Posts
    22
    ok, how about this then?....

    OrderID OrderLine ProdID
    -------------------------------------------
    10248 NULL 11
    10248 NULL 42
    10248 NULL 72
    10249 NULL 14
    10249 NULL 51
    10250 NULL 41
    10250 NULL 51
    10250 NULL 65
    10250 NULL 22
    10250 NULL 57
    10251 NULL 65
    10251 NULL 20
    10251 NULL 33

    Is that any (better) help?

    Thanks

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Perfect! I've just random numbers for ProdID, but the principle remains.
    Code:
    DECLARE @t AS  TABLE
        (OrderID INT, OrderLine INT, ProdID INT)
    
    INSERT INTO @t (OrderID, ProdID)
    SELECT 10248, 12 UNION ALL SELECT
    10248, 43 UNION ALL SELECT
    10248, 67 UNION ALL SELECT
    10249, 109 UNION ALL SELECT
    10249, 3 UNION ALL SELECT
    10250, 45 UNION ALL SELECT
    10250, 12 UNION ALL SELECT
    10250, 89 UNION ALL SELECT
    10250, 43 UNION ALL SELECT
    10250, 51 UNION ALL SELECT
    10251, 93 UNION ALL SELECT
    10251, 78 UNION ALL SELECT
    10251, 281
    
    UPDATE  dest
    SET     OrderLine       = increment
    FROM    @t AS dest
    INNER JOIN
            (
                SELECT  OrderID
                      , ProdID
                      , increment   = ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY ProdID)
                FROM    @t AS increments
            ) AS increments
    ON  increments.OrderID      = dest.OrderID
    AND increments.ProdID       = dest.ProdID
            
    SELECT  *
    FROM    @t AS dest
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2005
    Posts
    22
    Ok, so how do i actually do the first select query (see below) without knowing the data?

    Code:
    INSERT INTO @t (OrderID, ProdID)
    SELECT 10248, 12 UNION ALL SELECT
    10248, 43 UNION ALL SELECT
    10248, 67 UNION ALL SELECT
    10249, 109 UNION ALL SELECT
    10249, 3 UNION ALL SELECT
    10250, 45 UNION ALL SELECT
    10250, 12 UNION ALL SELECT
    10250, 89 UNION ALL SELECT
    10250, 43 UNION ALL SELECT
    10250, 51 UNION ALL SELECT
    10251, 93 UNION ALL SELECT
    10251, 78 UNION ALL SELECT
    10251, 281
    Thanks for the help.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't understand. Do you mean how do you update your real data?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Oct 2005
    Posts
    22
    yep, my real data.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The code I provided is as close a replica I can realistically create of your real environment, with enough sample data to prove it works.

    All you need is to take the update statement and change any table and column names to match your environment. The create table and insert statements are just me setting up the environment.

    You ok with that? Obviously test on dev data first.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Oct 2005
    Posts
    22
    That worked a treat.

    I was including the code above the update as well...........like a true numpty.

    Thanks for you help

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Pleased it worked out for you
    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
  •