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

    Unanswered: Update query help

    I have the following update query that does not work because the 1st subselect returns more than 1 row.

    Code:
    UPDATE STSC.SKU 
       SET (P_MINOHDLRQTY
          , P_SUNSETDATE) = (SELECT DECODE(b.p_minohdlrqty, a.rsqty, 0, b.p_minohdlrqty)   
                                  , DECODE(b.p_minohdlrqty, a.rsqty, b.p_sunsetdate, NULL) 
                               FROM PAC.RSL a 
                                  , STSC.SKU b
                             WHERE a.item     = b.item 
                               AND a.location = b.loc
                               AND a.enddate  < TO_DATE('07/11/05', 'DD/MM/YY')
                               AND a.activesw = 'Y'
                               AND a.location = '00067')
     WHERE (ITEM, LOC) IN (SELECT item, location
                             FROM  PAC.RSL
                            WHERE enddate < TO_DATE('07/11/05', 'DD/MM/YY')
                              AND activesw = 'Y'
                              AND location = '00067')
    The second subselect yields the rows I want to update:

    Code:
    ITEM                           LOCATION
    ------------------------------ ----------
    1317220R                      00067
    1336529                        00067
    1342345R                      00067
    1347617                        00067
    1353115                        00067
    1356244                        00067
    1362752                        00067
    1378358                        00067
    1388100                        00067
    1397766                        00067
    1606217                        00067
    1621930                        00067
    1633705                        00067
    
    13 rows selected.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Your first subselect needs to join to the current record being updated. To do this do the following

    Code:
    UPDATE STSC.SKU x
       SET (P_MINOHDLRQTY
          , P_SUNSETDATE) = (SELECT DECODE(b.p_minohdlrqty, a.rsqty, 0, b.p_minohdlrqty)   
                                  , DECODE(b.p_minohdlrqty, a.rsqty, b.p_sunsetdate, NULL) 
                               FROM PAC.RSL a 
                                  , STSC.SKU b
                                   WHERE <join b to x>
    ... rest of query
    Alan

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Perfect. Thanks!

Posting Permissions

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