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

Question

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

In Oracle have a look at merge

Aleksej
– Aleksej

2016-12-01 20:55:52 +00:00
Commented Dec 1, 2016 at 20:55 — Aleksej
– Aleksej, Commented Dec 1, 2016 at 20:55

Aleksej · Accepted Answer · 2016-12-01 21:09:31Z

3

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

answered Dec 1, 2016 at 21:09

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2016-12-01 22:15:34Z

This can be done with MERGE (in Oracle and most other DB systems - those that implement the MERGE statement from the SQL Standard). MERGE is the preferred solution in most cases.

There is a misconception that this cannot be done with an UPDATE statement in Oracle. I show below how it can be done - not to encourage its use (MERGE is better), but to show that UPDATE can be used as well. This is the transformation of the Postgre SQL the OP solicited in the original post.

update ( select t2.total_sales_person, t.total_count
          from   territory2_t t2 inner join
                 ( select salesterritoryid, count(*) as total_count
                   from   salesperson_t
                   group by salesterritoryid
                 ) t
                 on t2.territoryid = t.salesterritoryid
       )
set total_sales_person = total_count;

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related