Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create table specialties
(
    specialtyid serial primary key,
    specialty text not null unique check (specialty <> '')
);

insert into specialties(specialty)
     values ('Noorosurgery')          -- should be 'Neurosurgery'
          , ('Opthalmology')          -- should be 'Ophthalmology' 
          , ('Family Medicine');
         
select * 
  from specialties;

CREATE TABLE



INSERT 0 3




specialtyid
specialty


1
Noorosurgery

2
Opthalmology

3
Family Medicine



SELECT 3


create table doctors
(
    doctorid serial primary key,
    doctorname text not null unique check (doctorname <> ''), 
    specialtyid integer not null, 
    constraint doctors_specialty_fk foreign key (specialtyid)
        references specialties (specialtyid) match simple
        on update restrict
        on delete restrict
);

insert into doctors(doctorname,specialtyid)
    select 'Doctor-' || trim(to_char(n,'09'))
         , n%3+1
     from generate_series(0,12) gs(n);
    
select * 
  from doctors;

CREATE TABLE



INSERT 0 13




doctorid
doctorname
specialtyid


1
Doctor-00
1

2
Doctor-01
2

3
Doctor-02
3

4
Doctor-03
1

5
Doctor-04
2

6
Doctor-05
3

7
Doctor-06
1

8
Doctor-07
2

9
Doctor-08
3

10
Doctor-09
1

11
Doctor-10
2

12
Doctor-11
3

13
Doctor-12
1



SELECT 13


--- OFFICES not necessary for demo
create table referrals
(
    referralid serial primary key,
    officeid integer,
    doctorid integer,
    specialtyid integer,
--    constraint referrals_office_fk foreign key (officeid)
--        references offices (officeid) match simple
--        on update restrict
--        on delete restrict,
    constraint referrals_doctor_fk foreign key (doctorid)
        references doctors (doctorid) match simple
        on update restrict
        on delete restrict,
    constraint referrals_specialty_fk foreign key (specialtyid)
        references specialties (specialtyid) match simple
        on update restrict
        on delete restrict
);

insert into referrals(specialtyid)
    select n%3+1
      from generate_series(1,7) gs(n);
     
select * 
  from referrals; 

CREATE TABLE



INSERT 0 7




referralid
officeid
doctorid
specialtyid


1
null
null
2

2
null
null
3

3
null
null
1

4
null
null
2

5
null
null
3

6
null
null
1

7
null
null
2



SELECT 7


-- insert correct spelling (should just be an update) but
insert into specialties(specialty)
     values ('Neurosurgery')
          , ('Ophthalmology');

INSERT 0 2


-- so that both incorrect and incorrect spellings exist
insert into doctors(doctorname,specialtyid)
   select 'Doctor Bill'
        , specialtyid
     from specialties
    where specialty = 'Neurosurgery';

INSERT 0 1


-- Current Doctor Specility 
select d.doctorname, s.specialty
  from doctors  d
  left join specialties s
    on s.specialtyid = d.specialtyid 
order by doctorname;


doctorname
specialty


Doctor-00
Noorosurgery

Doctor-01
Opthalmology

Doctor-02
Family Medicine

Doctor-03
Noorosurgery

Doctor-04
Opthalmology

Doctor-05
Family Medicine

Doctor-06
Noorosurgery

Doctor-07
Opthalmology

Doctor-08
Family Medicine

Doctor-09
Noorosurgery

Doctor-10
Opthalmology

Doctor-11
Family Medicine

Doctor-12
Noorosurgery

Doctor Bill
Neurosurgery



SELECT 14


--  Apply corrections 
with corrections(old_spec, new_spec) as 
     ( select o.specialtyid, n.specialtyid 
         from specialties o
         join specialties n
           on (o.specialty = 'Noorosurgery' and n.specialty = 'Neurosurgery')
           or (o.specialty = 'Opthalmology' and n.specialty = 'Ophthalmology')
      )
   ,  do_doc as 
       ( update doctors 
            set specialtyid = new_spec
           from corrections
          where specialtyid = old_spec
          --returning new_spec
       ) 
   , do_ref as
       ( update referrals
            set specialtyid = new_spec
           from corrections
          where specialtyid = old_spec
          --returning new_spec
       )
delete from specialties
  where specialtyid in (select old_spec 
                          from corrections
                       );

DELETE 2


select * 
  from specialties ;


specialtyid
specialty


3
Family Medicine

4
Neurosurgery

5
Ophthalmology



SELECT 3


-- Corrected Doctor Specility 
select d.doctorname, s.specialty
  from doctors  d
  left join specialties s
    on s.specialtyid = d.specialtyid 
order by doctorname;


doctorname
specialty


Doctor-00
Neurosurgery

Doctor-01
Ophthalmology

Doctor-02
Family Medicine

Doctor-03
Neurosurgery

Doctor-04
Ophthalmology

Doctor-05
Family Medicine

Doctor-06
Neurosurgery

Doctor-07
Ophthalmology

Doctor-08
Family Medicine

Doctor-09
Neurosurgery

Doctor-10
Ophthalmology

Doctor-11
Family Medicine

Doctor-12
Neurosurgery

Doctor Bill
Neurosurgery



SELECT 14

referralid	officeid	doctorid	specialtyid
1	null	null	2
2	null	null	3
3	null	null	1
4	null	null	2
5	null	null	3
6	null	null	1
7	null	null	2

doctorid	doctorname	specialtyid
1	Doctor-00	1
2	Doctor-01	2
3	Doctor-02	3
4	Doctor-03	1
5	Doctor-04	2
6	Doctor-05	3
7	Doctor-06	1
8	Doctor-07	2
9	Doctor-08	3
10	Doctor-09	1
11	Doctor-10	2
12	Doctor-11	3
13	Doctor-12	1

doctorname	specialty
Doctor-00	Noorosurgery
Doctor-01	Opthalmology
Doctor-02	Family Medicine
Doctor-03	Noorosurgery
Doctor-04	Opthalmology
Doctor-05	Family Medicine
Doctor-06	Noorosurgery
Doctor-07	Opthalmology
Doctor-08	Family Medicine
Doctor-09	Noorosurgery
Doctor-10	Opthalmology
Doctor-11	Family Medicine
Doctor-12	Noorosurgery
Doctor Bill	Neurosurgery