Updating Multiple Rows with Data from Column A into Column B

oracleupdate

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.

Best Answer

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>>)

Related Solutions

Selecting data from multiple rows into a single row

Join the table to itself.

So:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.debit_status is not null
and yt2.customer is not null;

Or (if batch_number can reliably be used to fetch the 2 rows needed to make a single row:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.batch_number = 1
and yt2.batch_number = 2;

SQL fiddle.

Mysql – Updating multiple rows with different values in one query

UPDATE mytable SET
    fruit = CASE WHEN id=1 THEN 'orange' ELSE 'strawberry' END,
    drink = CASE WHEN id=1 THEN 'water'  ELSE 'wine'       END,
    food  = CASE WHEN id=1 THEN 'pizza'  ELSE 'fish'       END
WHERE id IN (1,2);

Personally, using CASE WHEN THEN END looks clumsy.

You could code this using the IF function.

UPDATE mytable SET
    fruit = IF(id=1,'orange','strawberry'),
    drink = IF(id=1,'water','wine'),
    food  = IF(id=1,'pizza','fish')
WHERE id IN (1,2);

Give it a Try !!!

CAVEAT : CASE WHEN THEN END is only handy when dealing with multiple values (more than 2)

Best Answer

Related Solutions

Selecting data from multiple rows into a single row

Mysql – Updating multiple rows with different values in one query

Related Question