Update through join

[Melancolie]

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:

update test_data as tda, test_def as tde
set test_pakke = tde.pakke
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.

[chandu031]

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:

update test_data as tda, test_def as tde
set test_pakke = tde.pakke
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.

[Melancolie]

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 :)

[debasisdas]

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.

[Melancolie]

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..