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

    Unanswered: Help with UPDATE query

    I have 2 tables, one called SOF and PROMO

    SOF TABLE:
    Code:
    ITEM     LOC     SOFDATE   DIFDATE     SOFMSG
    12345   U100   09/21/06   09/20/06    MESSAGE 1
    PROMO TABLE:
    Code:
    ITEM    LOC    MESSAGE
    1234   U100   TEST MESSAGE
    What I am trying to do with this query here is the following: 1) insert a new record with the details from the PROMO table if a corresponding record with the same ITEM, LOC does not exist in the SOF table;

    Code:
    INSERT INTO pac.sof
     (ver,    
      loc,
      splrcode_dlr,      
      ITEM, 
      sofqty, 
      sofloc,       
      sofdate, 
      difdate,
      sofmsg)
    SELECT  /*+ INDEX(s sku1) */  
    		'E01'             		   AS s_ver 
          , s.LOC             		   AS s_loc
          , s.p_splrcode_dlr  		   AS s_splrcode_dlr
          , s.ITEM            		   AS s_item 
          , 0                 		   AS s_sofqty
          , s.LOC             		   AS s_sofloc
          , TO_DATE('&4', 'dd/mm/yy')  AS s_sofdate
          , TO_DATE('&3', 'dd/mm/yy')  AS s_difdate
          , p.message	  			   AS s_message
      FROM stsc.SKU s
         , stsc.LOC l
         , stsc.ITEM i
         , pac.promo p
     WHERE s.LOC 	   = l.LOC
       AND s.ITEM      = i.ITEM
       AND s.LOC       = '&1'    
       AND s.p_InDIFSw = 1  -- this SKU needs to be in today's DIF file
       AND s.item      = p.ITEM
       AND s.loc       = p.LOCATION
       AND s.p_InDIFSw = 1
       AND (s.item, s.loc) NOT IN (SELECT item
                                        , loc 
                                     FROM PAC.SOF a 
    								WHERE a.item = s.item 
    								AND a.loc = s.loc
                                    AND a.sofdate  = TO_DATE('&4', 'dd/mm/yy')  --ohpost (DD/MM/RR)
                                    AND a.difdate  = TO_DATE('&3', 'dd/mm/yy'))  --difdate (DD/MM/RR)
    2) If a record exists in both tables with the same ITEM, LOC, update the SOF.SOFMSG with the PROMO.MESSAGE field
    Code:
    UPDATE PAC.SOF x
       SET x.sofmsg = (SELECT a.MESSAGE
                         FROM PAC.PROMO a
                            , PAC.SOF b
                        WHERE a.LOCATION = b.sofloc
                          AND a.item  	 = b.item  
                          AND a.LOCATION = '&1'                       --dealer
                          AND b.sofdate  = TO_DATE('&4', 'dd/mm/yy')                       AND b.difdate  = TO_DATE('&3', 'dd/mm/yy')) 
     WHERE (ITEM, LOC) IN (SELECT item, LOCATION
                             FROM  PAC.PROMO
                            WHERE LOCATION = '&1')
       AND x.sofdate  = TO_DATE('&4', 'dd/mm/yy')
       AND x.difdate  = TO_DATE('&3', 'dd/mm/yy')
    The first query appears to be working correctly, however there may be a more efficient way to do this, but the 2nd query is not working because the subselect is returning more than 1 row. What am I doing wrong?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >What am I doing wrong?
    Besides returning more than 1 row?
    Perhaps not using MERGE statement?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Does Oracle 8.0.6 support that statement?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Does Oracle 8.0.6 support that statement?
    I give up. Does it?
    No such RESTRICTION was included in original post.
    V8.0.6 has not been supported anytime this century.
    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Are you aware what your second select does? Especially the subquery in the SET clause?
    As it does not use any column from the updated table, it selects the same set of rows for all rows from the updated table.
    Seems you forgot to remove PAC_SOF b from the mentioned subquery and replace table aliases 'b' with 'x'.

  6. #6
    Join Date
    Sep 2005
    Posts
    220
    Thanks, that was the problem.

Posting Permissions

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