Solved

How do I update a single column for multiple rows?

Posted on 2007-11-14
7
2,478 Views
Last Modified: 2013-12-19
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?
0
Comment
Question by:CharleneS77
  • 4
  • 3
7 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 175 total points
ID: 20285162
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
 

Author Comment

by:CharleneS77
ID: 20285541
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
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 175 total points
ID: 20285571
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
ID: 20285574
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
 

Author Comment

by:CharleneS77
ID: 20285608
But when I run the query from above, both of the patients get their Adm_Type updated to 'IP'.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 175 total points
ID: 20285653
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
 

Author Comment

by:CharleneS77
ID: 20285719
Again, thank you for the clear response.  
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question