Results 1 to 6 of 6

Thread: update query

  1. #1
    Join Date
    Feb 2013
    Posts
    39
    Provided Answers: 2

    Unanswered: update query

    hi,

    i have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.

    for example

    update ps_po_lining b
    set y.recv_req = 'N'
    where recv_req in (Select y.recv_req
    FROM PS_PO_HDR X, PS_PO_lining Y, PS_PO_lining_SHIP Z, PS_PO_lining_DISTRIB D
    WHERE x.PO_STATUS NOT IN ('PX','C','X')
    AND x.PO_DT <= TO_DATE('2013-01-01','YYYY-MM-DD')
    AND x.BUSINESS_UNIT = y.BUSINESS_UNIT
    AND x.PO_ID = y.PO_ID
    AND y.RECV_REQ = 'Y'
    AND y.BUSINESS_UNIT = z.BUSINESS_UNIT
    AND y.PO_ID = z.PO_ID
    AND y.lining_NBR = z.lining_NBR
    AND z.DUE_DT < TO_DATE('2012-12-01','YYYY-MM-DD')
    AND z.BUSINESS_UNIT = D.BUSINESS_UNIT
    AND z.PO_ID = D.PO_ID
    AND z.lining_NBR = D.lining_NBR
    AND z.SCHED_NBR = D.SCHED_NBR
    AND D.ACCOUNT LIKE '6%');

    this query runs but its updaing 6000 rows. which is wrong. we need to update what ever the select query is retrieving.

    appreciated your help in this regard.
    Last edited by awais290; 02-20-13 at 17:51.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Your update query is not correct. You are referencing the alias Y but you are not specifying Y in your select from clause. That query could have never worked.
    Last edited by beilstwh; 02-20-13 at 17:10.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2013
    Posts
    21
    Quote Originally Posted by awais290 View Post
    hi,

    i have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.

    for example

    update ps_po_lining b
    set y.recv_req = 'N'
    where recv_req in (Select y.recv_req
    FROM PS_PO_HDR X, PS_PO_lining Y, PS_PO_lining_SHIP Z, PS_PO_lining_DISTRIB D
    WHERE x.PO_STATUS NOT IN ('PX','C','X')
    AND x.PO_DT <= TO_DATE('2013-01-01','YYYY-MM-DD')
    AND x.BUSINESS_UNIT = y.BUSINESS_UNIT
    AND x.PO_ID = y.PO_ID
    AND y.RECV_REQ = 'Y'
    AND y.BUSINESS_UNIT = z.BUSINESS_UNIT
    AND y.PO_ID = z.PO_ID
    AND y.lining_NBR = z.lining_NBR
    AND z.DUE_DT < TO_DATE('2012-12-01','YYYY-MM-DD')
    AND z.BUSINESS_UNIT = D.BUSINESS_UNIT
    AND z.PO_ID = D.PO_ID
    AND z.lining_NBR = D.lining_NBR
    AND z.SCHED_NBR = D.SCHED_NBR
    AND D.ACCOUNT LIKE '6%');

    this query runs but its updaing 6000 rows. which is wrong. we need to update what ever the select query is retrieving.

    appreciated your help in this regard.
    as i see your using two different alias for same table .
    try with b.recv_req.

  4. #4
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    i have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.
    We don't know your table structures, but it looks like you want to update the column(recv_req) to a 'N' if it is not already a 'N' and if a certain condition exists in the other tables.
    The reason I used 'exists' is because you don't need a list all you need is to check if the condition even exists at all.


    Try this:
    Code:
    update ps_po_lining b
      set  b.recv_req   =  'N'
    where  b.recv_req  !=  'N'
           exists ( select null
                     from  ps_po_hdr x
                     join  ps_po_lining         y on x.business_unit  =  b.business_unit
                                                 and x.business_unit  =  y.business_unit
                                                 and x.po_id          =  b.po_id
                                                 and x.po_id          =  y.po_id
                                                 and x.po_status not in( 'PX', 'C', 'X' )
                                                 and x.po_dt         <=  to_date( '2013-01-01', 'yyyy-mm-dd' )
                     join ps_po_lining_ship    z  on y.business_unit  =  z.business_unit
                                                 and y.po_id          =  z.po_id
                                                 and y.lining_nbr     =  z.lining_nbr
                                                 and z.due_dt         <  to_date( '2012-12-01', 'yyyy-mm-dd' )
                     join ps_po_lining_distrib d  on z.business_unit  =  d.business_unit 
                                                 and z.po_id          =  d.po_id
                                                 and z.lining_nbr     =  d.lining_nbr
                                                 and z.sched_nbr      =  d.sched_nbr
                                                 and d.account like '6%' );

  5. #5
    Join Date
    Feb 2013
    Posts
    39
    Provided Answers: 2
    Thanks for all replies. appreciated.. i got the idea. thanks again

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by spacebar View Post
    We don't know your table structures, but it looks like you want to update the column(recv_req) to a 'N' if it is not already a 'N' and if a certain condition exists in the other tables.
    The reason I used 'exists' is because you don't need a list all you need is to check if the condition even exists at all.


    Try this:
    Code:
    update ps_po_lining b
      set  b.recv_req   =  'N'
    where  b.recv_req  !=  'N'
           exists ( select null
                     from  ps_po_hdr x
                     join  ps_po_lining         y on x.business_unit  =  b.business_unit
                                                 and x.business_unit  =  y.business_unit
                                                 and x.po_id          =  b.po_id
                                                 and x.po_id          =  y.po_id
                                                 and x.po_status not in( 'PX', 'C', 'X' )
                                                 and x.po_dt         <=  to_date( '2013-01-01', 'yyyy-mm-dd' )
                     join ps_po_lining_ship    z  on y.business_unit  =  z.business_unit
                                                 and y.po_id          =  z.po_id
                                                 and y.lining_nbr     =  z.lining_nbr
                                                 and z.due_dt         <  to_date( '2012-12-01', 'yyyy-mm-dd' )
                     join ps_po_lining_distrib d  on z.business_unit  =  d.business_unit 
                                                 and z.po_id          =  d.po_id
                                                 and z.lining_nbr     =  d.lining_nbr
                                                 and z.sched_nbr      =  d.sched_nbr
                                                 and d.account like '6%' );
    I thought that it might be not necessary to repeat ps_po_lining in a subquery,
    and a qualifier "b." might be not necessary in SET clause(because, the table to be updated is clear).

    So, this might be better...
    Code:
    UPDATE ps_po_lining AS b
     SET   recv_req = 'N'
     WHERE recv_req = 'Y'
       AND EXISTS
           (SELECT null
             FROM
                   ps_po_hdr            AS x
             INNER JOIN
                   ps_po_lining_ship    AS z
              ON   z.business_unit  =  x.business_unit /* = b.business_unit */
               AND z.po_id          =  x.po_id         /* = b.po_id         */
               AND z.lining_nbr     =  b.lining_nbr
               AND z.due_dt         <  TO_DATE( '2012-12-01' , 'yyyy-mm-dd' )
             INNER JOIN
                   ps_po_lining_distrib AS d
              ON   d.business_unit  =  z.business_unit /* = b.business_unit */
               AND d.po_id          =  z.po_id         /* = b.po_id         */
               AND d.lining_nbr     =  z.lining_nbr    /* = b.lining_nbr    */
               AND d.sched_nbr      =  z.sched_nbr
               AND d.account      LIKE '6%'
             WHERE
                   x.business_unit  =  b.business_unit
               AND x.po_id          =  b.po_id
               AND x.po_status  NOT IN ( 'PX' , 'C' , 'X' )
               AND x.po_dt          <= TO_DATE( '2013-01-01' , 'yyyy-mm-dd' )
           )
    ;
    Last edited by tonkuma; 02-23-13 at 13:45. Reason: Rewote whole my sample code. Replace right parenthesis for the exists subquery.

Posting Permissions

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