How to update table with data from another table

Question

I have two tables

First table INTRA.TABLE_A

id  | first_value | second_value
----------------------------------
1   |    1234     |    181818
2   |    1235     |    1919191
3   |    1236     |    2384

Second table EXTRA.TABLE_B

id  | first_value | second_value
----------------------------------
1   |    1235     |    1919191
2   |    1236     |    0
3   |    1234     |    0
4   |    1234     |    181818
5   |    1234     |    0
6   |    1236     |    0

And I am trying to get data from INTRA.TABLE_A into EXTRA.TABLE_B with update:

UPDATE B
SET
    B.SECOND_VALUE = A.SECOND_VALUE
FROM
    EXTRA.TABLE_B B,
    INTRA.TABLE_A A
WHERE
    A.FIRST_VALUE = B.FIRST_VALUE
    AND B.SECOND_VALUE = 0;

But it "throws" syntax error at FROM word:

syntax_error

After update, result should looks like:

id  | first_value | second_value
----------------------------------
1   |    1235     |    1919191
2   |    1236     |    2384
3   |    1234     |    181818
4   |    1234     |    181818
5   |    1234     |    181818
6   |    1236     |    2384

I am using sqlDeveloper and Oracle database. How can I fix it?

Answer 1

Oracle does not support joins in update queries - unlike other database, such as SQL Server (in which the query you are using would probably run just as it is).

I would recommend a correlated subquery:

update table_b b
set second_value = (select a.second_value from tablea a where a.first_value = b.first_value)
where b.second_value = 0

You might want to add a condition to ensure that only "matching" rows are updated:

update table_b b
set second_value = (select a.second_value from tablea a where a.first_value = b.first_value)
where 
    b.second_value = 0
    and exists (select a.second_value from tablea a where a.first_value = b.first_value)