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?
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!
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!
Reply With Quote
Bookmarks