reverse SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: reverse SQL

  1. #1
    Join Date
    Oct 2000
    Posts
    139

    reverse SQL

    Hi

    I have several DELETE of a table, I need to write SQL statements which does the reverse, instead of deleting data I want to keep the data I need. For example

    Code:
    DELETE
    FROM SW_PERSON t
    WHERE EXISTS
    (SELECT 1
     FROM LISTA_ORDENES o
     WHERE o.order_id=t.swobjectid AND t.swtype='ORDER')
    /
    COMMIT
    /
    DELETE
    FROM SW_PERSON t
    WHERE t.swtype='ORDER' AND t.swobjectid IS NULL AND COMP_INST_ID IS NULL
    /
    COMMIT
    /
    DELETE
    FROM SW_PERSON t
    WHERE t.swtype IS NULL
    /
    COMMIT
    /
    DELETE
    FROM SW_PERSON t
    WHERE t.swtype='ORDER'
    AND t.swobjectid IS NULL 
    AND EXISTS
    (SELECT 1
     FROM  OM_COMPANY_INST c, LISTA_ORDENES l
     WHERE c.COMP_INST_ID=t.COMP_INST_ID
     AND l.order_id=c.order_id)
    /
    COMMIT
    /
    
    
    
    
    
    DELETE
    FROM OM_CONTRACT_TECHNICAL_SERV t
    WHERE EXISTS
    (SELECT 1 
    FROM OM_CONTRACT_INST c, OM_CONTRACT_SERV s, LISTA_ORDENES l  
    WHERE t.service_id=s.service_id 
    AND s.contract_id=c.contract_id 
    AND c.order_id=l.order_id)
    /
    COMMIT
    /
    DELETE
    FROM OM_CONTRACT_TECHNICAL_SERV t
    WHERE EXISTS
    (SELECT 1 
     FROM OM_CONTRACT_INST c,OM_CONTRACT_SERV s 
     WHERE t.service_id=s.service_id 
     AND s.contract_id=c.contract_id 
     AND c.order_id IS NULL)
    /
    COMMIT
    /
    
    
    
    
    DELETE
    FROM OM_CONTRACT_SERVICE t
    WHERE EXISTS
    (SELECT 1
    FROM LISTA_ORDENES l,OM_SERVICE_INSTANCE s ,OM_WELCOME_PACK w
    WHERE t.contract_service_id=w.contract_service_id 
    AND w.service_instance_id=s.service_instance_id 
    AND s.order_id=l.order_id 
    AND l.order_type_id<>3)
    /
    COMMIT
    /
    DELETE
    FROM OM_CONTRACT_SERVICE t
    WHERE EXISTS
    (SELECT 1
    FROM LISTA_ORDENES l,OM_EMF e, OM_WELCOME_PACK w
    WHERE t.contract_service_id=w.contract_service_id 
    AND w.emf_ext_id=e.external_id 
    AND e.order_id=l.order_id 
    AND l.order_type_id=3)
    /
    COMMIT
    /
    I need to write a SQL which does the reverse. I dont have a single clue. Itīs not that easy as I thought. I thought by reversing the conditions I would get things right but it does not seem so.

    Anyone with experiences do this sort of "Reversing" SQL?

    Cheers

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Huh? Maybe you could explain with an example...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2000
    Posts
    139
    Hi

    For example

    Code:
    DELETE
    FROM OM_CONTRACT_TECHNICAL_SERV t
    WHERE EXISTS
    (SELECT 1 
    FROM OM_CONTRACT_INST c, OM_CONTRACT_SERV s, LISTA_ORDENES l  
    WHERE t.service_id=s.service_id 
    AND s.contract_id=c.contract_id 
    AND c.order_id=l.order_id)
    /
    COMMIT
    /
    DELETE
    FROM OM_CONTRACT_TECHNICAL_SERV t
    WHERE EXISTS
    (SELECT 1 
     FROM OM_CONTRACT_INST c,OM_CONTRACT_SERV s 
     WHERE t.service_id=s.service_id 
     AND s.contract_id=c.contract_id 
     AND c.order_id IS NULL)
    /
    COMMIT
    /
    First delete, deletes 12 rows and second 22 rows and table has 50 rows so after running these deletes I will have 16 rows left.

    What I need to code is, a SQL which gives me the 16 rows left. Exactly the other way what deletes do!

    Cheers

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Maybe you just need to change "EXISTS" to "NOT EXISTS"?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Oct 2000
    Posts
    139
    Originally posted by slimdave
    Maybe you just need to change "EXISTS" to "NOT EXISTS"?

    Hi

    Thatīs what I thought but it doesnt seem so.
    Code:
    SQL> DELETE 
      2  FROM OM_ANNEX t
      3  WHERE EXISTS
      4  (SELECT 1 
      5  FROM LISTA_ORDENES l,OM_CONTRACT_INST c
      6  WHERE t.contract_id=c.contract_id 
      7  AND c.order_id=l.order_id)
      8  /
    
    40 rows deleted.
    
    SQL> DELETE 
      2  FROM OM_ANNEX t
      3  WHERE EXISTS(SELECT 1 FROM OM_CONTRACT_INST c 
      4                  WHERE t.contract_id=c.contract_id
      5                  AND c.order_id IS NULL)
      6  /
    
    3 rows deleted.
    SQL> select count(*) from OM_ANNEX;
    
      COUNT(*)
    ----------
          2769
    
    
    I need a SQL Which get me those 2769 rows.
    
      1  select count(*)
      2  FROM OM_ANNEX t
      3  WHERE NOT EXISTS
      4  (SELECT 1
      5  FROM LISTA_ORDENES l,OM_CONTRACT_INST c
      6  WHERE t.contract_id=c.contract_id
      7* AND c.order_id=l.order_id)
    SQL> /
    
      COUNT(*)
    ----------
          2772
    
    SQL> r
      1  select count(*)
      2  FROM OM_ANNEX t
      3  WHERE NOT EXISTS(SELECT 1 FROM OM_CONTRACT_INST c
      4                  WHERE t.contract_id=c.contract_id
      5*                 AND c.order_id IS NULL)
    
      COUNT(*)
    ----------
          2809
    If I run them seperately it returns good result, the problem is I need to insert the 2769 row into a tremporary table so I cannot run two inserts, I will end up with duplicate rows!

    Cant use UNION because it duplicate rows as well
    Last edited by Sweetie; 04-27-2004 at 11:00 AM.

  6. #6
    Join Date
    Oct 2002
    Posts
    807
    Logminer could help your cause as well. Look at the sql_undo column in v$logmnr_contents.

  7. #7
    Join Date
    Jul 2000
    Posts
    521
    Use MINUS.

    Example : Three table T1, T2 and T3.
    T1 Contains :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

    T2 contains :
    2
    3
    4
    5

    T3 contains :
    6
    7

    The Query :
    select * from t1
    minus
    select * from t2
    minus
    select * from t3

    will return :
    1
    8
    9
    10

    Works for you ???
    svk

  8. #8
    Join Date
    Oct 2000
    Posts
    139
    Hi

    I will give a try to both logminer and minus tomorrow, at home now cant carry out any test.

    thanks

  9. #9
    Join Date
    Oct 2000
    Posts
    139
    Hi

    Minus works

    Logminer does not work, ruled out

    The problem with minus is it takes ages and I am sure there are better ways :(

Posting Permissions

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



Click Here to Expand Forum to Full Width