Home » SQL & PL/SQL » SQL & PL/SQL » Updating table from anpther table ...Issue
Updating table from anpther table ...Issue [message #257671] Thu, 09 August 2007 00:15 Go to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
Update (select a.case_mgmt_provider_name
From master.ref_case_mgmt_provider a, commercial_prod.member_master b, commercial_prod.member_case_management c
where
b.payor_id =1 AND
b.center_id =1
AND b.stnd_member_id = 1
AND b.stnd_member_id = c.stnd_member_id
AND b.process_group_id = c.process_group_id
AND c.stnd_case_mgmt_provider_code = a.stnd_case_mgmt_provider_code)
Set a.case_mgmt_provider_name = 'Amul';

Can anyone tell me where i am going wrong in this statememnt?
Re: Updating table from anpther table ...Issue [message #257675 is a reply to message #257671] Thu, 09 August 2007 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tell us what is wrong with this statement.

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Updating table from anpther table ...Issue [message #257678 is a reply to message #257671] Thu, 09 August 2007 00:19 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone tell me where i am going wrong in this statememnt?
NO.
No Operating System name or version.
No Oracle version to 4 decimal places.
No code formatting (see http://www.orafaq.com/forum/t/59964/74940/)
No DDL.
No error message.
No help. [You're On Your Own (YOYO)!]

Re: Updating table from anpther table ...Issue [message #257701 is a reply to message #257678] Thu, 09 August 2007 01:01 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
Oracle Version 10.2.0.3.0

OS -Windows XP Professional Edition

Update (select a.case_mgmt_provider_name
From master.ref_case_mgmt_provider a, commercial_prod.member_master b, commercial_prod.member_case_management c
where
b.payor_id =1     AND
b.center_id =1
     AND   b.stnd_member_id = 1
     AND   b.stnd_member_id = c.stnd_member_id 
     AND   b.process_group_id = c.process_group_id
     AND   c.stnd_case_mgmt_provider_code =  a.stnd_case_mgmt_provider_code)
Set  a.case_mgmt_provider_name = 'Amul';


The error which i get is

Set a.case_mgmt_provider_name = 'Amul'
*
ERROR at line 10:
ORA-00904: "A"."CASE_MGMT_PROVIDER_NAME": invalid identifier

I would like to know where i have gone wrong in this update statement
Re: Updating table from anpther table ...Issue [message #257732 is a reply to message #257701] Thu, 09 August 2007 01:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
remove the alias while setting the value.

[Updated on: Thu, 09 August 2007 01:48]

Report message to a moderator

Re: Updating table from anpther table ...Issue [message #257751 is a reply to message #257732] Thu, 09 August 2007 02:11 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
i am running it in the commercial_prod schema


i did that and i am getting this error

ERROR at line 10:
ORA-01779: cannot modify a column which maps to a non key-preserved table

[Updated on: Thu, 09 August 2007 02:12]

Report message to a moderator

Re: Updating table from anpther table ...Issue [message #257753 is a reply to message #257751] Thu, 09 August 2007 02:12 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I anticipated that error. Search in this forum for key preserved update. It is been discussed loads of times.

[Updated on: Thu, 09 August 2007 02:13]

Report message to a moderator

Previous Topic: Insert into table in PL/SQL
Next Topic: How to drop more than one column at a time
Goto Forum:
  


Current Time: Mon Dec 05 13:19:43 CST 2016

Total time taken to generate the page: 0.12358 seconds