469,327 Members | 1,251 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

Update through join

Hey! I'm kinda new to oracle. I usually solve problems fast, but cant get this one.

I got 2 tables:

Test_data which has columns:
Test_navn
Test_pakke

Test_def which has columns:
Test_navn
Pakke

Test_data.test_pakke is unpopulated, and i want to populate it with Test_def.pakke where Test_navn of the 2 tables match.


From my MySQL days, i used this:

Expand|Select|Wrap|Line Numbers
  1. update test_data as tda, test_def as tde
  2. set test_pakke = tde.pakke
  3. where tda.test_navn = tde.test_navn
But i get a "ORA-00971: missing SET keyword" error... I get that no matter how i construct it.

The tables holds thousands of records, so i hope to get this working.. So hope you can help.
May 2 '07 #1
4 17514
chandu031
78 Expert
Hey! I'm kinda new to oracle. I usually solve problems fast, but cant get this one.

I got 2 tables:

Test_data which has columns:
Test_navn
Test_pakke

Test_def which has columns:
Test_navn
Pakke

Test_data.test_pakke is unpopulated, and i want to populate it with Test_def.pakke where Test_navn of the 2 tables match.


From my MySQL days, i used this:

Expand|Select|Wrap|Line Numbers
  1. update test_data as tda, test_def as tde
  2. set test_pakke = tde.pakke
  3. where tda.test_navn = tde.test_navn
But i get a "ORA-00971: missing SET keyword" error... I get that no matter how i construct it.

The tables holds thousands of records, so i hope to get this working.. So hope you can help.

Hi,

In oracle there is no update join as in MySQL where you can update two tables in a single statement. A workaround would be to use corelated query.
Your query should look something like this:

UPDATE TEST_DATA A
SET TEST_PAKKE = (SELECT PAKKE FROM TEST_DEF B WHERE A.TEST_NAVN = B.TEST_NAVN)

This should do the trick, although it will perform slowly when there are a very large number of records.
May 2 '07 #2
Thanks Chandu :)

However i found the solution just 2 minutes after i posted...

Almost similiar to yours..

update test_data a
set test_pakke = (select distinct pakke from test_def b where a.test_navn = b.test_navn)


Only took a few mins for 13421 records :)
May 2 '07 #3
debasisdas
8,127 Expert 4TB
He said it will perform slowly because performance wise Corelated sub-queries are slowest.

Because while normal sub-query is evaluated only once for the table a
co-related sub-query is evaluated once per each row in the table

Again it depends on some other physical factors(hardware) also.
May 3 '07 #4
He said it will perform slowly because performance wise Corelated sub-queries are slowest.

Because while normal sub-query is evaluated only once for the table a
co-related sub-query is evaluated once per each row in the table

Again it depends on some other physical factors(hardware) also.
Thats correct :) But it was a one time update to expand a table and drop another one, so performance wasnt an issue..
May 3 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Narine | last post: by
17 posts views Thread by kalamos | last post: by
2 posts views Thread by Mike Leahy | last post: by
2 posts views Thread by Sim Zacks | last post: by
3 posts views Thread by Michel Esber | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.