Posted by Ed Murphy on 11/30/06 05:58

Brian Link wrote:

> Three tables: A_Appointment, AX_Appointment_Entity and E_Entity.
> AX_Appointment_Entity is an intersect/association table between
> A_Appointment and E_Entity. One appointment may have many attendees
> (Appointment_Entity). One attendee (Entity) may have many
> appointments.
>
> My task is to delete all appointments and related
> AX_Appointment_Entity rows where the number of attendees is one or
> less.

If deletions from A_Appointment cascade to AX_AppointmentEntry:

delete from A_Appointment
where AppointmentKey in (
select AppointmentKey
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
)

If deletions from A_Appointment are blocked until the matches in
AX_Appointment_Entity are also deleted:

select AppointmentKey
into #appointments_to_delete
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1

delete from AX_Appointment_Entity
where AppointmentKey in (
select AppointmentKey
from #appointments_to_delete
)

delete from A_Appointments
where AppointmentKey in (
select AppointmentKey
from #appointments_to_delete
)

If deletions from A_Appointment leave orphans in AX_Appointment_Entity
(why?):

delete from A_Appointment
where AppointmentKey in (
select AppointmentKey
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
)

delete from Ax_Appointment_Entity
where AppointmentKey in (
select AppointmentKey
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
)

[Back to original message]