Solved

How do I update a single column for multiple rows?

Posted on 2007-11-14
7
2,476 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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now