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

Huh? Maybe you could explain with an example...

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

Maybe you just need to change "EXISTS" to "NOT EXISTS"?

Quote:

---

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

Logminer could help your cause as well. Look at the sql_undo column in v$logmnr_contents.

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 ???

Hi

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

thanks

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 :(