Wednesday, June 2, 2010

Tuesday, June 1, 2010

Oracle : Delete & Update Queries Using Joins

The SQL's are consutructed using dummy table.. below are the just framework. 
Modify according to your  requirement

Delete Query :-
---------------------

Delete from table1 A
where exists (select * from  table2 B
WHERE A.EMPLID = B.EMPLID AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND
B.Col5 = 6556 AND B.Col_status = 'S')


Update Query:-
-------------

update table2 A set A.STATUS = 'V'
where exists (Select * from table1 B
WHERE A.EMPLID = B.EMPLID AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND
B.col5 = 6556 AND B.col_status = 'S')


Thanks
Srinivas