The doctor died, how to reschedule meetings with SQL?

Question

I am really new to SQL. I hope this is a clear question. I am doing a small project for myself, I am moving from problem to problem, trying to learn something new. I made an access file that I update through SQL using C #.

Before the doctor removes from the database, I want to transfer his existing appointments to other doctors who have time on the date of the initial appointment.

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

For the ID_Patient value, I will use the ID_Patient table, vice versa for the Doctor value.

I was looking for updating the ID_Doctor value with the help of this other doctor who has no assignments for data and time, but creating code from scratch is hard for me and with a little help I hope to understand this much earlier.

Any help would be greatly appreciated, thanks!

Answer 1

Assuming this is the whole theory (of course, the best ways to do this in a large volume DB!). But to learn 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 the appointment table (alias: a), where the Appointments are for the identifier of the deceased doctor. He sets the doctor’s identifier for these appointments to be the first identifier of a doctor who did not receive an appointment at that time.

Note. You can improve sql by changing the line: da.Time = a.Time to be longer than the time range, so you do not have assignments that are planned very close to each other.

Answer 2

since this refers to a medical database that has fairly strong HIPAA rules governing it when it comes to code that accesses it, you might want to leave it to the developer. also, even without any scintilla of the existing code, which you cannot post in any case, because it belongs to the medical database, we have no starting point to help you.

Answer 3

To replace doctor 42 at all of your 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: Do not use SO code for medical manufacturing software, lol)

Answer 4

You say Access, but are you using Jet or access itself? If so, you can use the query design window to configure custom queries, and then switch to SQL View to see how they work. The SQL rows from this should be used in C # with very little modification. I would expect something like:

 UPDATE Appointments SET ID_Doctor = 2 WHERE ID_Doctor = 1