sqloracle-databasesql-updateora-00933

Oracle Update Query using Join


I am trying to update the amount using Join but getting exception:

UPDATE tab1
   SET tab1.total_adjusted_cost = tab1.total_adjusted_cost + t1.total
 FROM table1 tab1, 
      (SELECT tab3.name, tab3.add, SUM(tab2.amount) AS total
         FROM table2 tab2,
              table3 tab3,
              table4 tab4
        WHERE tab2.id = tab3.id
          AND tab3.id = tab4.id
          AND tab4.indicator = 'Y'
        GROUP BY tab3.name, tab3.add ) t1
WHERE tab1.id = t1.id;


SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 -  "SQL
command not properly ended"

Solution

  • Try to use merge

    merge into table1 tab1 
    using
    (
    SELECT tab3.name, tab3."add", SUM(tab2.amount) AS total
      FROM table2 tab2,
        table3 tab3 ,
        table4 tab4
      WHERE tab2.id        = tab3.id
      AND tab3.id            = tab4.id
      AND tab4.indicator             ='Y'
      GROUP BY tab3.name,
        tab3."add"
    )t1
    on(tab1.id      = t1.id)
    when matched then 
    update set tab1.total_adjusted_cost = tab1.total_adjusted_cost + t1.total