Updating a table in Oracle by inner query

Question

I have the following query, which works in Sybase, and I need help to rewrite it for Oracle. I am new to Oracle.

update table1 
set col1 = (select sum(col3) from table2 t2 where t2.id = t1.id and t2.col <> 'on')
from table1 t1 
where t1.id > 1 and t1.col5 in (12,13) and exists 
(select id from table2 t2 where t2.id = t1.id and t2.col <> 'on' and col3 > 0)

When I tried to execute in Oracle I am getting missing expression error

https://stackoverflow.com/questions/tagged/oracle+sql-update+join — , Aug 27 '20 at 14:39
Looking at the query, If you just remove the `from table1 t1 ` and add alias to update clause like `update table1 t1` should work — Sujitmohanty30, Aug 27 '20 at 15:50
@sujai, does it make sense to put it as an answer ? You can do so and accept your own answer. — Sujitmohanty30, Sep 01 '20 at 07:29
@Sujitmohanty30 any idea on this https://stackoverflow.com/questions/63684910/error-in-oracle-query-single-row-subquery-returns-more-than-one-row/63685084#63685084 — sujai, Sep 01 '20 at 09:55

score 2 · Answer 1 · answered Aug 27 '20 at 13:21

In Oracle you can't use From clause like this:

There are three options in Oracle to update table based on another table:

Update it as an updateview

UPDATE (SELECT t1.id, 
               t1.name name1,
               t1.desc desc1,
               t2.name name2,
               t2.desc desc2
          FROM table1 t1,
               table2 t2
         WHERE t1.id = t2.id)
   SET name1 = name2,
       desc1 = desc2

Update by wrting a subquery i.e. correlared update

UPDATE table1 t1
   SET (name, desc) = (SELECT t2.name, t2.desc
                         FROM table2 t2
                        WHERE t1.id = t2.id)
 WHERE EXISTS (
    SELECT 1
      FROM table2 t2
     WHERE t1.id = t2.id )

Update using Merge statement without insert:

MERGE INTO table1 t1
USING
(
-- For more complicated queries you can use WITH clause here
SELECT * FROM table2
)t2
ON(t1.id = t2.id)
WHEN MATCHED THEN UPDATE SET
t1.name = t2.name,
t1.desc = t2.desc;

Below is the Oracle official update documentation

Above examples are taken from this post

When I tried Update by wrting a subquery i.e. correlared update, its keep on executing — sujai, Aug 27 '20 at 13:45
Could you please update your question with correlated query and explain the logic and if possible share some sample data. — Atif, Aug 27 '20 at 13:50

score 1 · Accepted Answer · answered Sep 01 '20 at 07:44

This worked for me

 update table1 t1
 set t1.col1 = (select sum(col3) from table2 t2 where t2.id = t1.id and t2.col <> 'on')
 where t1.id > 1 and t1.col5 in (12,13) and exists 
 (select id from table2 t2 where t2.id = t1.id and t2.col <> 'on' and col3 > 0)

Updating a table in Oracle by inner query

2 Answers2