Results 1 to 5 of 5

Thread: UPDATE Query

  1. #1
    Join Date
    May 2006
    Posts
    20

    Unanswered: UPDATE Query

    My input from a table X is
    Code:
    ref_nbr       prod  usoc  tn                    order_nbr    date
    0004352641 LPIC  LPIC  8187899951517 C62767057 10/29/2007            
    0004352642 LDPIC LDPIC 8187899951517 C62767057 10/29/2007            
    0004352634 CID   CNM   8187899951517 C62767057 10/29/2007            
    0004352635 CID   CNM   8187899951517 C62767057 10/29/2007            
    0004352636 CCS   ESM   8187899951517 C62767057 10/29/2007            
    0004352637 CCS   NWL   8187899951517 C62767057 10/29/2007            
    0004352638 CCS   RAF   8187899951517 C62767057 10/29/2007            
    0004352639 LPIC  LPIC  8187899951517 C62767057 10/29/2007            
    0004352640 LDPIC LDPIC 8187899951517 C62767057 10/29/2007            
    0004352631 LB19B LB19B 8187899951517 C62767057 10/29/2007            
    0004352632 KSTTU KSTTU 8187899951517 C62767057 10/29/2007            
    0004352633 PGOHN PGOHN 8187899951517 C62767057 10/29/2007

    I have written SQL to find the duplicates for the combination of
    prod,usoc,tn,order_nbr and date as below

    Code:
    SELECT A.PROD,A.USOC,A.TN,B.ORDER_NBR,B.DATE
    FROM abc A,
         xyz B,
         jkl C
    WHERE A.REF_NBR = B.REF_NBR
    AND   B.REF_NBR = B.INWD_REF_NBR
    AND   C.REF_NBR = A.REF_NBR
    AND   (B.SO_CMPL_DATE >= '01-OCT-2007'
    AND   B.SO_CMPL_DATE <= '30-NOV-2007')
    AND   B.STATUS_CD <> 'DEL'
    AND   B.SPECIAL_PROCESS_IND IN ('UPR1 ','UPR2 ','UPR3 ')
    GROUP BY A.PROD,A.USOC,A.TN,B.ORDER_NBR,B.DATE
    HAVING COUNT(*) > 1
    After this I need to update the STATUS code for those Duplicate records to DEL by using the REF_NBR match

    I am not able to retrieve the REF_NBR from the above query which is unique

    any help is appreciated

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Something like this?
    Code:
    update your_table set
      status = 'DEL'
      where ref_nbr in (select a.ref_nbr
                        from abc a, xyz, jkl
                        where ...
                        group by ...
                        having count(*) > 1
                       );
    However, I'm not sure I understood what "ref_nbr which is unique" means (so query might need tweaking).

  3. #3
    Join Date
    May 2006
    Posts
    20
    Let me clear some more points
    Table ABC
    Code:
    ref_nbr       prod  usoc  tn                    order_nbr    date
    0004352641 LPIC  LPIC  8187899951517 C62767057 10/29/2007            
    0004352642 LDPIC LDPIC 8187899951517 C62767057 10/29/2007            
    0004352634 CID   CNM   8187899951517 C62767057 10/29/2007            
    0004352635 CID   CNM   8187899951517 C62767057 10/29/2007            
    0004352636 CCS   ESM   8187899951517 C62767057 10/29/2007            
    0004352637 CCS   NWL   8187899951517 C62767057 10/29/2007            
    0004352638 CCS   RAF   8187899951517 C62767057 10/29/2007            
    0004352639 LPIC  LPIC  8187899951517 C62767057 10/29/2007            
    0004352640 LDPIC LDPIC 8187899951517 C62767057 10/29/2007            
    0004352631 LB19B LB19B 8187899951517 C62767057 10/29/2007            
    0004352632 KSTTU KSTTU 8187899951517 C62767057 10/29/2007            
    0004352633 PGOHN PGOHN 8187899951517 C62767057 10/29/2007

    I want to check the duplicates for the combination of A.PROD,A.USOC,A.TN,B.ORDER_NBR,B.DATE

    Table XYZ is having STATUS column
    I want to get the ref_nbr's to update this for the combination of above mentioned duplicates

    For example shown above
    Code:
    prod  usoc          tn     order_nbr     date        count(*)
    LDPIC LDPIC 8187899951517 C62767057 10/29/2007              2                        
    CID   CNM   8187899951517 C62767057 10/29/2007             2        
    LPIC  LPIC  8187899951517 C62767057 10/29/2007             2
    If I do update with Ref_nbr I should get only 6 records should update
    How I can achieve this?

  4. #4
    Join Date
    May 2006
    Posts
    20
    Quote Originally Posted by Littlefoot
    Something like this?
    Code:
    update your_table set
      status = 'DEL'
      where ref_nbr in (select a.ref_nbr
                        from abc a, xyz, jkl
                        where ...
                        group by ...
                        having count(*) > 1
                       );
    However, I'm not sure I understood what "ref_nbr which is unique" means (so query might need tweaking).

    If I execute the above query I am getting a.ref_nbr is not a GROUP_BY Expression..

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you tried to execute the exact query as posted above, you should really consider reading manual about use of the SELECT statement, as well as GROUP BY and HAVING clauses. Once you make the SELECT work, move on to UPDATE.

Posting Permissions

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