asked on

oracle - query to delete from the table

select a.site_code
from dept_staff a,sites b
where a.site_code = b.site_code
and schedule_date = '12-feb-2012'
and reason_code = 'SH'
and MOBILE_OR_SUBCENTER = 'M';
--------------

SITE_CODE
MR14
CW31
OF03
CY21
RL02
CW31
CY21
CY21
A262
RL02
OF03
OF03
RL02
CY21
RL02
CY21
CW31
OF03
OF03
CW31
CY21
MR14
OF03
RL02
OF03
CW31
RL02
RL02
MR14
CY21
RL02
OF03
MR14
A262
OF03
RL02
--------------
select count(a.site_code) into v_site_code_count
from dept_staff a,sites b
where a.site_code = b.site_code
and schedule_date = '12-feb-2012'
and reason_code = 'SH'
and MOBILE_OR_SUBCENTER = 'M';
If v_site_code_count > 0 then
case msgbox.show('Conflicting Dates','A schedule exists for this chosen week.'||chr(10)||'Do you want to Delete?', 'note', 'NO', 'YES')
when ALERT_BUTTON2 then
msgbox.show('Delete', 'Deleting conflicting schedules..., Please wait....', 'ok');

delete from dept_staff
where schedule_date = :date_screen.sunday_date
and site_code = .............;

commit;
My question is how do I loop through all the site codes? for that schedule_date and for that site code, I have to delete from dept_staff.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

first - this is bad practice.

and schedule_date = '12-feb-2012'

sorry , I did that to post the query. Actuall the date is entered by the user as a date parameter. And based on the date enterred I need to get the other values. That date is schedule_date

anumoses

ASKER

thanks