-
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...
Jeff Hunter
-
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"?
-
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.
-
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 ???
svk
-
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 :(
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|