Solved

How do I update a single column for multiple rows?

Posted on 2007-11-14
7
2,481 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 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 74

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

717 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