SQL Query Help

Discussion in 'Software' started by Mada_Milty, Dec 21, 2007.

  1. Mada_Milty

    Mada_Milty MajorGeek

    Hi All,

    Was hoping for a little direction on a SQL query I'm trying to make up.

    I have a table similar to this:

    ID-----SEQUENCE NUMBER-----HOURS
    A------10----------------------------------NULL
    A------20----------------------------------NULL
    A------30----------------------------------NULL
    B------15----------------------------------NULL
    B------20----------------------------------NULL
    C------1-----------------------------------NULL
    C------30----------------------------------NULL
    C------50----------------------------------NULL

    What I need to do is update the HOURS of the lowest sequence number for every ID.

    Ex.
    A/10/120
    B/15/120
    C/1/120

    I've managed to get a select query to return the records, but the update query is frustrating me.

    TIA for any input!

    Milty
     
  2. Wookie

    Wookie Sergeant Major

    I think this is what your looking for


    UPDATE <table>
    SET HOURS = 100
    WHERE SEQUENCE IN (SELECT MIN(SEQUENCE) AS expr1 FROM <table> GROUP BY ID)

    This is for SQL2005, we can also do this with a join if your DB doesn't support subqueries
     
  3. Mada_Milty

    Mada_Milty MajorGeek

    Thank you Wookie, that gets me closer than I had gotten, but this still isn't quite right.

    The query you've provided will update ALL the sequence numbers that are in the set of minimum sequence numbers. I ran this against my table, and it has updated:

    All records with sequence numbers:
    1, 5, 10, 15, 20, 25, 30, 32, 35, 40, 50, 70, 100, 110, 120

    And skipped records with sequence numbers:
    3, 7, 8, 11, 12, 16, 17, 18, 26, 27, 28, 37, 38, 45, 55, 57, 58, 60, 61, 62, 63, 65, 80, 82, 85, 90, 130, 140

    This is close to what I need, but I need to update the hours column of the row with just the lowest sequence number for each ID number, as opposed to the hours of every lowest sequence number.
     
  4. Mada_Milty

    Mada_Milty MajorGeek

    Alright! I've got it! I actually broke it down into two queries, but this gets the job done.

    First, I select the records into a new table:

    Code:
    SELECT SYSADM_OPERATION.WORKORDER_TYPE, SYSADM_OPERATION.WORKORDER_BASE_ID, Min(SYSADM_OPERATION.SEQUENCE_NO) AS MINOPSEQNO INTO LOWEST_OP_SEQ_NO
    FROM SYSADM_OPERATION
    GROUP BY SYSADM_OPERATION.WORKORDER_TYPE, SYSADM_OPERATION.WORKORDER_BASE_ID
    HAVING (((SYSADM_OPERATION.WORKORDER_TYPE)="M"));
    
    Then, I use this new table to link to the main table, and update the appropriate records:

    Code:
    UPDATE LOWEST_OP_SEQ_NO INNER JOIN SYSADM_OPERATION ON LOWEST_OP_SEQ_NO.WORKORDER_BASE_ID = SYSADM_OPERATION.WORKORDER_BASE_ID SET SYSADM_OPERATION.MOVE_HRS = 120
    WHERE (((SYSADM_OPERATION.WORKORDER_BASE_ID)=[LOWEST_OP_SEQ_NO].[WORKORDER_BASE_ID]) AND ((SYSADM_OPERATION.SEQUENCE_NO)=[LOWEST_OP_SEQ_NO].[MINOPSEQNO]));
    
     
  5. Wookie

    Wookie Sergeant Major

    Ah cool, sorry for the lack of response I was messing with it then the holidays got in the way hence my absence the past week. I was actually thinking of writing a script to do it lol but your way works nicely

    good work!
     
  6. Mada_Milty

    Mada_Milty MajorGeek

    Hey man, no need to apologize; beggars can't be choosers!

    Thanks for taking the time!
     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds