Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: update query help

    I need help with an update query to update this data:
    Code:
    ITEM                  NON_STOCKING_LOC        OLD_OH       NEW_OH     ATL	LAN	NEW	ROC	SEA	TOR	OKC
    000188ALU                      LAN 		200		0	N	N	N	N	N	N	N  
    000188ALU                      ATL 		310		0	N	N	N	N	N	N	N  
    000188ALU                      NEW 		10		0	N	N	N	N	N	N	N  
    000188ALU                      ROC 		270		0	N	N	N	S	N	N	N  
    000188ALU                      SEA 		90		0	N	N	N	N	N	N	N  
    000188ALU                      TOR 		110		0	N	N	N	N	N	N	N
    Into this. Basically take sum of old_oh for all records where the indicator (ATL, LAN, NEW, etc) does not equal 'S' and add that amount to the new_oh where the indicator does equal 'S'
    Code:
    ITEM                  NON_STOCKING_LOC        OLD_OH       NEW_OH     ATL	LAN	NEW	ROC	SEA	TOR	OKC
    000188ALU                      LAN 		200		0	N	N	N	N	N	N	N  
    000188ALU                      ATL 		310		0	N	N	N	N	N	N	N  
    000188ALU                      NEW 		10		0	N	N	N	N	N	N	N  
    000188ALU                      ROC 		270		990	N	N	N	S	N	N	N  
    000188ALU                      SEA 		90		0	N	N	N	N	N	N	N  
    000188ALU                      TOR 		110		0	N	N	N	N	N	N	N

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    I'm not currently connected to database so I can not test it, but try something like below: BTW to be honest, I'm not clear with your request but according to your example result I would suggest following:

    Code:
    update table_name
    set 
    NEW_OD = (SELECT SUM(OLD_OH) 
                     FROM table_name
                     WHERE ATL != 'S'
                     OR     LAN != 'S'
                     OR     NEW != 'S'
                     OR     ROC != 'S'
                     OR     SEA != 'S'
                     OR     TOR != 'S'
                     OR     OKC != 'S'
                    )
    WHERE ATL = 'S'
    OR     LAN = 'S'
    OR     NEW = 'S'
    OR     ROC = 'S'
    OR     SEA = 'S'
    OR     TOR = 'S'
    OR     OKC = 'S';
    In case this is not what you need, try to specify your request

Posting Permissions

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