CODEWITHSUNDEEP
sqloracle-databaseplsql
Bram
Bram

Reputation: 89

PLSQL - Update statement with variable

I am currently trying to write an update statement using a variable (which will be expanded to use multiple variables later on).

Currently I have the following statement (altered for posting here), but I am running into an error and I don't immediately see the mistake I am making:


DECLARE 
v_var1 table1.CY_VALUE % TYPE;
BEGIN

SELECT SUM(Column1 + Column2) 
INTO v_var1
FROM table2
WHERE survey_ID = 1 AND Active_Flag = 1
GROUP BY SURVEY_ID;

UPDATE table1
SET CY_VALUE = v_var1
WHERE SURVEY_ID = 1 AND KPI_ID = 1;

END;

This is the error I am receiving:

SQL Error [6550] [65000]: ORA-06550: line 15, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following:

:= . ( @ % ;

What do I need to alter in order to get this to work in this example? Or are there better ways to write the update for table 1, containing a sum from table 2?

Upvotes: 1

Views: 3689

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

There is nothing obviously wrong with your code. But why not just use a correlated subquery and dispense with the variable -- and the PL/SQL?

UPDATE table1 t1
    SET CY_VALUE = (SELECT SUM(t2.Column1 + t2.Column2)
                    FROM table2 t2
                    WHERE t2.survey_ID = t1.survey_ID AND
                          t2.Active_Flag = 1
                   )
    WHERE SURVEY_ID = 1 AND KPI_ID = 1;

Although not related to your error, the GROUP BY in the first query is also misleading. An aggregate query with a GROUP BY can return any number of rows -- including 0. Instead, just lead out the GROUP BY:

SELECT SUM(Column1 + Column2) 
INTO v_var1
FROM table2
WHERE survey_ID = 1 AND Active_Flag = 1;

The WHERE clause filters down to the survery_ID you want and without the GROUP BY this query always returns exactly one row.

Upvotes: 2

Related Questions