One to many delete help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

One to many delete help

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-03-25 : 18:20:02

I want to delete a staff member, and I also have to delete their entries from the appointment table and the appRecurrence table. The appointment table is linked to the staff table by appointment.appStaffID = staff.staffID, and appRecurrence table linked to the appointment table by appRecurrence.appID = appointment.appID.

The trouble I'm having is how can I delete the values in the appRecurrence table?

Edited by - benricho on 03/25/2002 18:22:27

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-25 : 19:01:29

Hi

You need two delete statements.



Declare @staffID int

SET @staffId = 1

DELETE FROM appRecurrence
FROM --note the second from clause
appRecurrence
INNER JOIN appointment ON appointment.appID = appRecurrence.appID
INNER JOIN staff ON staff.staffID = appointment.appStaffId
WHERE
staff.staffID = @staffID

-- Note the last join is a little redundant, just there to demonstate the technique

DELETE FROM appointment WHERE appStaffID = @staffID

Hope that helps

Damian

benricho
Yak Posting Veteran

84 Posts

Posted - 2002-03-25 : 19:08:25

Thanks Damien, I didn't know about the second FROM clause technique, worked like a charm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources