Update in pl/sql procedure

Question

I am writing the following code in plsql oracle to update the rating of seller and product as avg rating as given in order_products table:

create or replace procedure update_seller_product
as

begin
  update product set rating=
     (select rating from 
        (select p_id,avg(rating) as rating 
           from order_products 
          group by p_id
        ) as t2
      )
    where product.p_id=t2.p_id; 
  commit;
end;
/

but it is giving following error:

Statement ignored Error at line 4: PL/SQL: ORA-00907: missing right parenthesis

Why? Please help

Does this answer your question? [ORA-00907: missing right parenthesis](https://stackoverflow.com/questions/24580780/ora-00907-missing-right-parenthesis) — Bishan, Mar 31 '21 at 08:26
yes the error is same but i have written very simple code which is working correctly on sql but not in oracle pl/sql — SITANSHU YADAV, Mar 31 '21 at 08:45

score 1 · Accepted Answer · answered Mar 31 '21 at 09:05

Remove as for the alias:

CREATE OR REPLACE PROCEDURE update_seller_product
AS
BEGIN
   UPDATE product
      SET rating =
             (SELECT rating
                FROM (  SELECT p_id, AVG (rating) AS rating
                          FROM order_products
                      GROUP BY p_id) t2)        --> here
    WHERE product.p_id = t2.p_id;

   COMMIT;
END;
/

In Oracle, AS can be used for columns (but doesn't have to):

SQL> select dummy as dm,           --> with AS
  2         dummy    dm2           --> without it
  3  from dual;

D D
- -
X X

For tables, it must not be used:

SQL> select dummy from dual as d;
select dummy from dual as d                     --> with AS
                       *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> select dummy from dual d;                  --> without it

D
-
X

Update in pl/sql procedure

1 Answers1