?
Solved

How do I update a single column for multiple rows?

Posted on 2007-11-14
7
Medium Priority
?
2,483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 700 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 74

Assisted Solution

by:sdstuber
sdstuber earned 700 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

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 74

Assisted Solution

by:sdstuber
sdstuber earned 700 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses

801 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