How do I update a single column for multiple rows?

I am trying to update a single column for multiple rows.  

select * from PATIENT_ADMISSION;

PATIENT_ID     SSNO                ADM_DATE                                DISC_DATE            ADM_TY
111111         999999999       14-NOV-07 05.50.31.351960 PM                                         OP
111112         888888888       14-NOV-07 05.50.31.368973 PM                                         OP


create table PATIENT_ADMISSION_NEW as
select * from PATIENT_ADMISSION;

Table created.


update PATIENT_ADMISSION_NEW
set Adm_Type = 'IP';

2 rows updated.


update PATIENT_ADMISSION pa1
set Adm_Type = (
select Adm_Type
from PATIENT_ADMISSION pa2
where pa1.Adm_Type = pa2.Adm_Type);

select Adm_Type
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row



drop table PATIENT_ADMISSION_NEW;

Table dropped.


How can I update a single column with a different value for each row?
CharleneS77Asked:
Who is Participating?
 
sdstuberCommented:
your query is close,  your subselect needs to pull the data from pa2 for a single row for each row in pa1.

Your query...
update PATIENT_ADMISSION pa1
set Adm_Type = (
select Adm_Type
from PATIENT_ADMISSION pa2
where pa1.Adm_Type = pa2.Adm_Type);

says for each row in pa1,  look through the same table (you didn't use your "_new" table)
and pull the adm_type for all rows that have the same adm_type as the current adm_type.

So, for each row in patient_admission, with type OP update the type to two rows of type OP. Two rows, because both rows already have type OP.

That, of course, doesn't make sense.

What I think you meant to do was for each row in the old table
find the corresponding row in the new table and update the old table's type to be the same as the corresponding row's type in the new table.

If so, this is what you are looking for....

update PATIENT_ADMISSION pa1
set Adm_Type = (
select Adm_Type
from PATIENT_ADMISSION_new pa2
where pa1.patient_id = pa2.patient_id);
0
 
CharleneS77Author Commented:
Thank you for giving me a good description of my mistake and showing me the correct way.
(Just so you know, the Adm_Type column is set to 'OP' by default.)  
So, in my initial attempt, I was "attempting" to update every row where Adm_Type='OP' (which would be every single row) to Adm_Type='IP'.

That's not what I meant to do though.  I am trying to update each row with the Adm_Type value that corresponds with the particular patient.  
0
 
sdstuberCommented:
well, to update all OP to IP

you don't even need the sub query

update patient_admission
set adm_type = 'IP' where adm_type = 'OP'


That's all you need
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
but for your particular patient scenario.  assuming your patient id's are unique
and the ones you want to update are in the "new" table

then the query I sent above should work
0
 
CharleneS77Author Commented:
But when I run the query from above, both of the patients get their Adm_Type updated to 'IP'.
0
 
sdstuberCommented:
of course.

your "new" table has both records with adm_type 'IP'

so looking up 111111 you find IP in new and update OP in old to IP.
then looking up 111112 you find IP in new (again) and update OP in old to IP (again)

If you want to see someting different happen, update your new table
so that each row has different data in it.

update patient_admission_new set adm_type = 'AA' where patient_id = 111111
update patient_admission_new set adm_type = 'BB' where patiend_id = 111112

then run

update PATIENT_ADMISSION pa1
set Adm_Type = (
select Adm_Type
from PATIENT_ADMISSION_new pa2
where pa1.patient_id = pa2.patient_id);


your patient_admission table will then show AA on 111111 and BB on 111112
0
 
CharleneS77Author Commented:
Again, thank you for the clear response.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.