database question

Discussion in 'OT Technology' started by vwgeekdotcom, Jul 9, 2004.

  1. vwgeekdotcom

    vwgeekdotcom Silver Surfer

    Joined:
    Sep 6, 2001
    Messages:
    1,012
    Likes Received:
    0
    Location:
    Maine
    I need help with a query,
    lets say that there are columns a-g
    if colum c-e are the same, than I want the info in column g changed to the info in column b in the first record of that group
    I have like items (sku's) grouped in my database, and i want to create a blanket part number for skus that have matching descriptions, and add that part number in a new column at the end of the grouped SKU's record

    this is what i start out with

    a b c d e f g
    2 4 5 6 9 8
    2 5 5 6 9 9
    2 7 5 6 0 5
    1 2 3 4 5 6
    1 3 3 4 5 7
    1 4 3 4 5 8
    1 5 3 4 5 9

    i want to end up with
    a b c d e f g
    2 4 5 6 9 8 4
    2 5 5 6 9 9 4
    2 7 5 6 0 5 7
    1 2 3 4 5 6 2
    1 3 3 4 5 7 2
    1 4 3 4 5 8 2
    1 5 3 4 5 9 2

    thanks in advance.
    -tim
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    C, D and E? Or just C and E? And what is the first one in the group - is this sorted data?
     
  3. vwgeekdotcom

    vwgeekdotcom Silver Surfer

    Joined:
    Sep 6, 2001
    Messages:
    1,012
    Likes Received:
    0
    Location:
    Maine
    C,D, AND E.
    Yes, the data is sorted. I can give you an example if you need one.
    Thanks alot
    tim
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    First you need to determine what G is going to be, by finding out each group's B (doesn't matter whether it's duplicated or not - G is always going to be the group's B):

    Code:
    SELECT First(Table1.b) AS FirstOfb, Table1.c, Table1.d, Table1.e INTO FirstOfB
    FROM Table1
    GROUP BY Table1.c, Table1.d, Table1.e;
    This makes a table Called FirstOfB. Then all you do is update G:

    Code:
    UPDATE Table1 INNER JOIN FirstOfB ON (Table1.e = FirstOfB.e) AND (Table1.d = FirstOfB.d) AND (Table1.c = FirstOfB.c) SET Table1.g = [firstofb];
     
  5. vwgeekdotcom

    vwgeekdotcom Silver Surfer

    Joined:
    Sep 6, 2001
    Messages:
    1,012
    Likes Received:
    0
    Location:
    Maine
    Thanks alot, I will try this when i get home, so this will bring all of the numbers over, with teh correct (b) for each one?
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yeah - unless you've missed some logic, all I did was group all of the records by c,d, and e and then asked it to tell me what's the first b in each group. It didn't matter that record 3 didn't duplicate, I still needed to know what to set g, and the logic of finding out what each group's B still applies.
     
  7. vwgeekdotcom

    vwgeekdotcom Silver Surfer

    Joined:
    Sep 6, 2001
    Messages:
    1,012
    Likes Received:
    0
    Location:
    Maine
    Thanks alot 5Gen. I plan to post some more in here, i see alot of stuff I could help on in this forum, i just happen to be new to db's
    -tim
     

Share This Page