Home » SQL & PL/SQL » SQL & PL/SQL » update query for oracle (oracle10g)
update query for oracle [message #441012] Thu, 28 January 2010 06:04 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi mine is a sql server query where migrating data required in oracle

UPDATE EMRDiagnosisCodesLkup
SET DIAGNOSIS_CODE     =b.DIAGNOSIS_CODE,
  DIAGNOSIS_DESCRIPTION=b.DIAGNOSIS_DESCRIPTION
FROM EMRDiagnosisCodesLkup a,
  (SELECT DIAGNOSIS_ID,
    REPLACE(diagnosis_code,'.','')          AS DIAGNOSIS_CODE,
    REPLACE(DIAGNOSIS_DESCRIPTION,'.-','-') AS DIAGNOSIS_DESCRIPTION
  FROM EMRDiagnosisCodesLkup
  WHERE DIAGNOSIS_CODE LIKE '%.'
  ) b
WHERE a.DIAGNOSIS_ID=b.DIAGNOSIS_ID

Re: update query for oracle [message #441015 is a reply to message #441012] Thu, 28 January 2010 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what did you try to port it?
Or are you just waiting we make your job?

Regards
Michel
Re: update query for oracle [message #441016 is a reply to message #441012] Thu, 28 January 2010 06:10 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
what we have to do with it...


sriram Smile
Re: update query for oracle [message #441020 is a reply to message #441016] Thu, 28 January 2010 06:21 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi my columns DIAGNOSIS_CODE and DIAGNOSIS_DESCRIPTION records are like R61.1 then it should be like R61.1
if it is R61. then it should come like R61.

so what all records are about both columns need to be updated like above
Re: update query for oracle [message #441022 is a reply to message #441020] Thu, 28 January 2010 06:24 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
rajasekhar857 wrote on Thu, 28 January 2010 17:51
hi my columns DIAGNOSIS_CODE and DIAGNOSIS_DESCRIPTION records are like R61.1 then it should be like R61.1
if it is R61. then it should come like R61.

so what all records are about both columns need to be updated like above



Your message totally confusing me ...
Its already in the format why again?whats the difference there?

sriram Smile
Re: update query for oracle [message #441024 is a reply to message #441022] Thu, 28 January 2010 06:27 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
sorry if it is R61. then like R61 removing .
Re: update query for oracle [message #441025 is a reply to message #441024] Thu, 28 January 2010 06:29 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Ok do that you already know what you have to update
then do that.
Whats hurting you in this?
What you tried?what you got?any error?

sriram Smile
Re: update query for oracle [message #441026 is a reply to message #441012] Thu, 28 January 2010 06:29 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I have no idea what this code does, anyway it looks strange EMRDiagnosisCodesLkup is mentioned three times (with one self join). Anyway, to updating row based on values from the same row is as simple as
UPDATE EMRDiagnosisCodesLkup
SET DIAGNOSIS_CODE     =REPLACE(diagnosis_code,'.',''),
  DIAGNOSIS_DESCRIPTION=REPLACE(DIAGNOSIS_DESCRIPTION,'.-','-')
WHERE DIAGNOSIS_CODE LIKE '%.';

As this is not your first usage of UPDATE statement, you should have a look at correct Oracle UPDATE syntax into SQL Reference. It is available e.g. online on http://tahiti.oracle.com/
And yes, your feedback on another threads (e.g. this one) would be appreciated.
Re: update query for oracle [message #441033 is a reply to message #441026] Thu, 28 January 2010 06:39 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it seems we are just here to do his job and then go to h...

Regards
Michel
Previous Topic: PL/SQL procedure - filling empty fields as a rule (merged 3)
Next Topic: last_value not working in Oracle 9i
Goto Forum:
  


Current Time: Tue Dec 06 00:22:39 CST 2016

Total time taken to generate the page: 0.11740 seconds