reverse SQL

[Sweetie]

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.

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