DELETING FROM A SELECT QUERY 1

[klornpallier]

I have the query below what gives me the records I desire. I easily need to delete all of these records. How would I do this with a DELETE FROM clause or a PL/SQL cursor? Any help would be appreciated!

SELECT ded.emp_no , re.pay_group
, ded.element
,ded.bal_increasing_cash
,ded.bal_increasing_units
,ded.pay_stop_date
FROM psadmin.payded_condition ded
,psadmin.refno re
WHERE NOT EXISTS
(SELECT 1 FROM psadmin.award_element awd
,psadmin.position_element pos
WHERE (ded.element = awd.element
OR ded.element = pos.element)
AND awd.award = 'CAR')
AND ded.emp_no = re.payroll_emp_no
AND pay_group IN ('CRE', 'HME')
AND (ded.bal_increasing_cash = 0
OR bal_increasing_units = 0)
AND perm_cash = 0

 

[LKBrwnDBA]

Try something similar to this:

DELETE FROM psadmin.payded_condition d0
 WHERE (d0.emp_no, d0.element) = (
        SELECT ded.emp_no, ded.element 
          FROM psadmin.payded_condition ded 
                 ,psadmin.refno        re 
                 WHERE NOT EXISTS 
                (SELECT 1 FROM psadmin.award_element  awd 
                            ,psadmin.position_element pos 
          WHERE (ded.element = awd.element 
                  OR ded.element = pos.element)
                  AND awd.award = 'CAR')  
                AND ded.emp_no = re.payroll_emp_no 
                AND pay_group IN ('CRE', 'HME') 
                AND (ded.bal_increasing_cash = 0
                OR bal_increasing_units = 0)
                AND perm_cash = 0)
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Maybe replace the ...

WHERE (d0.emp_no, d0.element) = (

With

WHERE (d0.emp_no, d0.element) IN (



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sem]

You need a query to be key-preserving to delete right from it.

In any case you may delete from single table only.



Regards, Dima

http://seminihin.narod.ru