Home » SQL & PL/SQL » SQL & PL/SQL » Update statement from 2 table with copied value (merged) (oracle 10g)
icon12.gif  Update statement from 2 table with copied value (merged) [message #425008] Wed, 07 October 2009 02:47 Go to next message
yacho98
Messages: 3
Registered: October 2009
Location: Malaysia
Junior Member

Hi all,

I'm trying to update table value and get the value from other table but fail. Below is the step


create table temp_ref
(id int,
val int
);
insert into temp_ref values (1,1);

create table temp_val
(id integer,
id_temp_ref int,
new_val int
);

insert into temp_val (id,id_temp_ref) values (1,1);

SELECT * FROM temp_ref;
id val
----- ------
1 1
SELECT * FROM temp_val;
id id_temp_ref new_val
----- -------------- ----------
1 1 (null)

update temp_val a, temp_ref b
set a.new_val = b.val
where a.id_temp_ref = b.id;

syntax error at or near "a"


What is the problem with my update statement?


Thank for your help
Re: Update statement from 2 table with copied value (merged) [message #425010 is a reply to message #425008] Wed, 07 October 2009 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What is the problem with my update statement?

It is not a valid statement for Oracle.
Have a look at documentation.

You can use an "update (select...) set ..." if you have primary/unique key on the tables or a merge statement.

Regards
Michel
Re: Update statement from 2 table with copied value (merged) [message #425021 is a reply to message #425008] Wed, 07 October 2009 03:35 Go to previous messageGo to next message
yacho98
Messages: 3
Registered: October 2009
Location: Malaysia
Junior Member

I try to use below statement but wrong value updated

update temp_val
set new_val = (select distinct b.val from temp_val a, temp_ref b where a.id_temp_ref = b.id)
where exists
(select 1 from temp_val a, temp_ref b where a.id_temp_ref = b.id)

result
id id_temp_ref new_val
----- -------------- ----------
1 1 1
1 2 1


The result i want is
id id_temp_ref new_val
----- -------------- ----------
1 1 1
1 2

Hope someone can help

thanks
Re: Update statement from 2 table with copied value (merged) [message #425024 is a reply to message #425021] Wed, 07 October 2009 03:51 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

You can write


UPDATE temp_val a
   SET new_val = (SELECT b.val
                    FROM temp_ref b
                   WHERE a.id_temp_ref = b.ID)


here I assume that id_temp_ref is always unique

Regards
Prajakta001
Re: Update statement from 2 table with copied value (merged) [message #425027 is a reply to message #425024] Wed, 07 October 2009 04:16 Go to previous message
yacho98
Messages: 3
Registered: October 2009
Location: Malaysia
Junior Member

Ok its work. Thanks
Previous Topic: Sort a String and Prepare Range
Next Topic: problem with dbms_file_transer
Goto Forum:
  


Current Time: Sun Dec 04 04:17:31 CST 2016

Total time taken to generate the page: 0.11119 seconds