CODEWITHSUNDEEP
oracle-databaseplsql
SITANSHU YADAV
SITANSHU YADAV

Reputation: 27

Update in pl/sql procedure

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

Upvotes: 0

Views: 61

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

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

Upvotes: 1

Related Questions