user1815210 user1815210 - 3 days ago 7
SQL Question

Oracle SQL: The equivalent way to execute the follwing expression from PostgreSQL?

The following thread successfully showed how to use a an UPDATE SET and FROM clause together, to update an entire row of a specific column with a value derived from a different table.

When executing following expression in Oracle SQL:

UPDATE territory2_t
SET total_sales_person = t.total_count
FROM (
SELECT salesterritoryid, count(*) as total_count
FROM salesperson_t
group by salesterritoryid
) t
WHERE territoryid = t.salesterritoryid;


Oracle states: SQL Error: ORA-00933: SQL command not properly ended

Answer

In Oracle you can use merge to do the job:

merge into territory2_t
using (
       SELECT salesterritoryid, count(*) as total_count
       FROM salesperson_t
       group by salesterritoryid
      ) t
on (territoryid = t.salesterritoryid)
when matched then
    update SET total_sales_person = t.total_count
Comments