Updating Multiple Rows with Data from Column A into Column B?

Question

I have about 60,000 rows that I'm needing to update the information from column_a to column_b. For example, 60,000 customers need their arrival_time to match their departure_time. I understand that I can do this with one row and it work with a nested select statement in an update statement. When trying to update multiple rows though I believe I'm getting stuck on having to have 60,000 unique key identifiers.

This was what I used to update 1 of the records, I'm using Oracle SQL:

UPDATE patient 
SET discharge_dt = (SELECT admit_dt 
 FROM patient 
 WHERE pat_seq = 'XXXXXX') 
WHERE facility_id = 'X' 
 AND pat_seq = 'XXXXXX'

Apologize for the confusing description, not really a database administrator myself.

Any help on this would be greatly appreciated.

Answer 1

If you just want the discharge_dt to be overwritten with the admit_dt from the same row, it sounds like you just need

UPDATE patient
 SET discharge_dt = admit_dt
 WHERE facility_id = 'X'
 AND pat_seq IN (<<query that returns the 60,000 keys>>)

• Philᵀᴹ: Shouldn't that be an IN rather than an =?
• Justin Cave: @Phil - Yup. Fixed.
• SQLSavant: Thanks for this, not sure why I didn't see this yesterday but came in this morning and looked at the statement and knew what to do. Oh well, I blame lack of coffee! Thanks!

Answer 2

Cloyd, the description is indeed confusing.

I'll state some assumptions and work on them. Please, shall you consider this worth, comment out and I'll keep editing the answer until we get 'there'. Deal?

Let's go:

• the update statement has as condition facility_id='X'

• the sub-query at the assignment has no condition at all (except pat_seq)

• therefore, I'm assuming and 'forecasting' that:

  • pat_seq is key for identification of a patient

  • both admit_dt and discharge_dt are of type date/time, not just date

  • the facility_id column at patient table keeps track of the several facilities within which the patient has been sent during it's (sad?) stay

  • for any patient, for each facility where it's been sent to, there has to be a row keeping track of date/time of admission and discharging, like:

    • pat_seq: 0015, facility_id: AABB, admit_dt: 15:35, discharge_dt:16:45
    • pat_seq: 0015, facility_id: CCA1, admit_dt:16:45, discharge_dt:17:10
    • pat_seq: 0015, facility_id: AAHD, admit_dt:17:10, discharge_dt: NULL

  • just the admitances are registered, hence for any new admitance registered there has to be an update action upon the imediate previous entry for which the discharge time will be assumed as the admitance time within the next facility

  • bullseye?

Well, for this scenario, you shall do, assuming a batch, scheduled task:

UPDATE patient 
SET discharge_dt = (
 /*
 For every entry past this, retrieve the least admit_dt, which shall identify the moment the pat_seq moved into the next facility
 */
 SELECT MIN(admit_dt)
 FROM patient pat_next_admit
 WHERE pat_next_admit.pat_seq = patient.pat_seq
 AND pat_next_admit.admit_dt > patient.admit_dt
 ) 
WHERE discharge_dt IS NULL /* No need to process rows already processed! */