Home » SQL & PL/SQL » SQL & PL/SQL » table column to be updated (oracle 10g)
table column to be updated [message #412240] Wed, 08 July 2009 06:08 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi i am having two tables
select * from EMRPATIENTRACEETHNICITY

PATIENT_ID           RACE_ETHNICITY_ID      MODIFIED_DATE             
-------------------- ---------------------- ------------------------- 
1503                 4                      26-FEB-09                 
1508                 8                      27-FEB-09                 
1530                 2                      04-MAR-09                 
1501                 8                      10-MAR-09                 
1560                 4                      10-MAR-09                 
1629                 4                      12-MAR-09                 
1636                 1                      14-MAR-09                 
1653                 2                      13-MAR-09                 
1588                 4                      12-MAR-09                 
1516                 1                      13-MAR-09                 
1730                 2                      18-MAR-09         

select * from emrpatientsmaster

PATIENT_ID           PATIENT_GENDER       RACE_ID                ETHNICITY_ID           
-------------------- -------------------- ---------------------- ---------------------- 
1508                 1                                                                  
1538                 1                                                                  
1539                 1                                                                  
1564                 1                                                                  
1646                 1                                                                  
1672                 2                                                                  
1695                 2                                                                  
1703                 1                    901                                           
1730                 1                                                                  
1749                 2                    4                                             
1755                 2                                                                  
1782                 2                                                                  
1789                 2                                                                  




can uou help me out i want to update emrpatientsmaster table column race_id values to be based on null and 0. from the above table
Re: table column to be updated [message #412246 is a reply to message #412240] Wed, 08 July 2009 06:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you mean by:
Quote:
alues to be based on null and 0


What's wrong with:
UPDATE emrpatientsmaster m
SET race_id = nvl((SELECT race_ethnicity_id
                   FROM   EMRPATIENTRACEETHNICITY e
                   WHERE  e.patient_id = m.patient_id),race_id)
Re: table column to be updated [message #412247 is a reply to message #412240] Wed, 08 July 2009 06:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OK, I appreciate that English (probably) isn't your first language, but you really, really need to concentrate and try to improve the way that you ask questions.
Quote:
can uou help me out i want to update emrpatientsmaster table column race_id values to be based on null and 0. from the above table
is far, far too vague. You need to provide a test case (which you have been asked for before many times) as well as expected results and and explanation of those requirements.
If you cannot do this, you are unlikely to get an answer from anyone (and i can name a few that you will definitely not get an answer from - including me)
Re: table column to be updated [message #412259 is a reply to message #412247] Wed, 08 July 2009 06:51 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
sorry for my engilsh.
iam having 12 values totally.
query is like this.

SELECT Patient_id,
    MAX(Race_Ethnicity_id)
  FROM EMRPATIENTRACEETHNICITY
  WHERE patient_id IN
    (SELECT patient_id
    FROM EMRPATIENTRACEETHNICITY
    GROUP BY patient_id
    HAVING COUNT(*)>1
    )
  GROUP BY Patient_id



the values retrieved from above query has to be deleted from
this table EMRPATIENTRACEETHNICITY
Re: table column to be updated [message #412260 is a reply to message #412259] Wed, 08 July 2009 07:03 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Last try:
Quote:
You need to provide a test case (which you have been asked for before many times) as well as expected results and and explanation of those requirements.
If you cannot do this, you are unlikely to get an answer from anyone (and i can name a few that you will definitely not get an answer from - including me)
Re: table column to be updated [message #412263 is a reply to message #412260] Wed, 08 July 2009 07:23 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
UPDATE emrpatientsmaster b
SET b.race_id =
  (SELECT race_ethnicity_id
  FROM
    (SELECT a.patient_id,
      b.patient_id,
      a.race_ethnicity_id,
      b.race_id
    FROM EMRPATIENTRACEETHNICITY a,
      emrpatientsmaster b
    WHERE a.patient_id = b.patient_id
    AND (b.race_id    IS NULL
    OR b.race_id       = 0)
    )
  )


Hi iam facing error message for the above

like
Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"
help me on this issue
Re: table column to be updated [message #412269 is a reply to message #412263] Wed, 08 July 2009 07:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The subquery returns more than one row.

Why is impossible to tell, since you refuse to provide a test case.
Re: table column to be updated [message #412271 is a reply to message #412263] Wed, 08 July 2009 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The subquery must returns only one row otherwise how Oracle would know which it will assign to the field?

Regards
Michel
Re: table column to be updated [message #412278 is a reply to message #412271] Wed, 08 July 2009 07:53 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

hi,
You'll have to help the people first if you are expecting them to help you. They'll be happy if you provide them the scripts like this:
create table EMRPATIENTRACEETHNICITY
(PATIENT_ID number,
RACE_ETHNICITY_ID NUMBER,
MODIFIED_DATE DATE)
/

create table emrpatientsmaster .......

script for any constraints .....

insert statements for the data in the tables......

then your query and the problem you are facing.....
Re: table column to be updated [message #412420 is a reply to message #412263] Thu, 09 July 2009 04:34 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
1.You are using the name b twice once inside SELECT and one in UPDATE
2.this below code is an example of how you can achieve it

 UPDATE emrpatientsmaster d
 SET d.race_id =
   (SELECT race_ethnicity_id
   FROM
     (SELECT a.patient_id,
       b.patient_id,
       a.race_ethnicity_id,
       b.race_id
     FROM EMRPATIENTRACEETHNICITY a,
       emrpatientsmaster b
     WHERE a.patient_id = b.patient_id
       and d.patient_id = b.patient_id
     AND (b.race_id    IS NULL   OR b.race_id       = 0)
     )
   )
Re: table column to be updated [message #412469 is a reply to message #412240] Thu, 09 July 2009 10:39 Go to previous message
yoursrajju
Messages: 5
Registered: September 2006
Location: Bangalore
Junior Member
Hi,

The above UPDATE statement likely to update all the rows in table emrpatientsmaster, which should not be the case i guess. Please include a WHERE clause that updates the correct row.
Previous Topic: Insert statement
Next Topic: SQL
Goto Forum:
  


Current Time: Sat Dec 03 06:16:41 CST 2016

Total time taken to generate the page: 0.06797 seconds