Doctor died, how to reschedule appointments with SQL?

I am really new to SQL i hope this is an understandable question. I am doing a little project for myself, i go from problem to problem trying to learn new things. i have made a Access file which i update trough SQL with 开发者_C百科c#.

Before a doctor is removed from the database i want to reschedule his existing appointments to other doctors that have time at the date of the original appointment.

My APPOINTMENT table has the following columns in it: ID_Appointments, ID_Doctor, ID_Patient, Date, Time and a column notes.

For the ID_Patient value i will be using the table ID_Patient, vice versa for the Doctor value.

I've been looking into updating the ID_Doctor value with this of another doctor that does not have an appointment at the given data and time but creating the code out of scratch is hard for me and with a little help i hope to understand it much sooner.

Any help will be much appreciated, thanks!

Assuming this is all theory (certainly better ways to do this in a high volume DB!!). But for learning SQL the following should do the trick.

UPDATE a
SET ID_Doctor = (SELECT TOP 1 ID_Doctor FROM ID_Doctor d
                 WHERE NOT EXISTS (SELECT 1 FROM Appointment da
                                   WHERE da.ID_Doctor = d.ID_Doctor
                                   AND da.Date = a.Date AND da.Time = a.Time)
                )
FROM Appointment a
WHERE a.ID_Doctor = <The ID of the Doctor who Died>

Explination: It Updates your Appointment table (alias: a) where the Appointments are for the ID of the doctor that died. It sets the id of the doctor for those appointments, to be the first id of a doctor that has not got an appointment at that time.

Note: You could improve the sql by changing the line: da.Time = a.Time to be more of a time range, so that you dont have appointments that are scheduled extremely close together.

since this relates to a medical database, which has rather strong HIPAA regulations governing it when it comes to the code that accesses it, you may well want to leave this to a developer. also without even a scintilla of existing code, which you can't post anyeways because it belongs to a medical database, we have no starting point in helping you.

To replace doctor 42 on all of his appointments, try:

update  Appointments
set     ID_Doctor = 
        (
        select  top 1 ID_Doctor
        from    Doctors d
        where   d.Alive = 1
                and not exists
                (
                select  *
                from    Appointments a
                where   d.ID_Doctor = a.ID_Doctor
                        and Appointments.Time = a.Time
                )
        )
where   ID_Doctor = 42
        and PatientSignedImmunityFormForInjuriesCausedByUntestedSQL = 1

(Disclaimer: don't use SO code for medical production software, lol)

You say Access, but are you using Jet or is Access itself installed? if it is, you can use the query design window to set up sample queries and then switch to SQL View to see how they work. The SQL strings from this should be usable in c# with very little modification. I would expect something like:

UPDATE Appointments SET ID_Doctor = 2 
WHERE ID_Doctor = 1