Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    12

    Unanswered: Updating all records in a group

    Hello again (and thanks in advance for your help)

    I have a table that i have created row numbers for
    In the sample below all records for item AAAA would have a groupMax value of 31417 and BBBB would be 4632. I have thousands of items, so i need an update statement. I have been struggling with this final piece for project for a while. I have tried various row_number options, and of course you can use max in an update statement etc...

    help before I am bald from pulling my hair out.

    Item,GroupNo,recno,GroupCount,GroupMax
    AAAA ,1,24949,1,0
    AAAA ,1,25873,2,0
    AAAA ,1,26797,3,0
    AAAA ,1,27721,4,0
    AAAA ,1,28645,5,0
    AAAA ,1,29569,6,0
    AAAA ,1,30493,7,0
    AAAA ,1,31417,8,0
    BBBB ,1,936,1,0
    BBBB ,1,1860,2,0
    BBBB ,1,2784,3,0
    BBBB ,1,3708,4,0
    BBBB ,1,4632,5,0

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I am not 100% sure of your requirements (you might wish to rethink your question and provide the expected result also) but here's what I think you want.

    Select the value you want for each item
    Code:
    SELECT item
         , Max(recno) As new_value
    FROM   your_table
    GROUP
        BY item
    Join this back to your main table to show the new value against each row
    Code:
    SELECT *
    FROM   your_table
     INNER
      JOIN (
            SELECT item
                 , Max(recno) As new_value
            FROM   your_table
            GROUP
                BY item
           ) As calculated_values
        ON calculated_values.item = your_table.item
    Then you just have to turn that in to an UPDATE statement
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2012
    Posts
    12
    Yeah the update statement is where I have the issue, because I need to do it for each item/group and you cant use max in an update statement.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you run the queries I provided?

    You'll note that the fields you are accessing on the outermost SELECT are not using Max()
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2012
    Posts
    12
    Thank-you very much it worked !!!!

Posting Permissions

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