40

I need to check a condition. i.e:

if (condition)> 0 then
  update table
else do not update
end if

Do I need to store the result into a variable using select into?

e.g:

declare valucount integer
begin
  select count(column) into valuecount from table
end
if valuecount > o then
  update table
else do 
  not update
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
user1339913
  • 1,017
  • 3
  • 15
  • 36

3 Answers3

56

You cannot directly use a SQL statement in a PL/SQL expression:

SQL> begin
  2     if (select count(*) from dual) >= 1 then
  3        null;
  4     end if;
  5  end;
  6  /
        if (select count(*) from dual) >= 1 then
            *
ERROR at line 2:
ORA-06550: line 2, column 6:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
...
...

You must use a variable instead:

SQL> set serveroutput on
SQL>
SQL> declare
  2     v_count number;
  3  begin
  4     select count(*) into v_count from dual;
  5
  6     if v_count >= 1 then
  7             dbms_output.put_line('Pass');
  8     end if;
  9  end;
 10  /
Pass

PL/SQL procedure successfully completed.

Of course, you may be able to do the whole thing in SQL:

update my_table
set x = y
where (select count(*) from other_table) >= 1;

It's difficult to prove that something is not possible. Other than the simple test case above, you can look at the syntax diagram for the IF statement; you won't see a SELECT statement in any of the branches.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • how to set a different value to x if "where" condition fails? – Satyam Aug 22 '15 at 17:57
  • However, you can run a select as a line of code in an if or else branch when the entire statement is terminated with [end if;]. When the if statement is not properly terminated, the developer interface just states that the select statement will be skipped, a very vague error. – Joseph Poirier Oct 28 '19 at 17:18
3

Edit:

The oracle tag was not on the question when this answer was offered, and apparently it doesn't work with oracle, but it does work with at least postgres and mysql

No, just use the value directly:

begin
  if (select count(*) from table) > 0 then
     update table
  end if;
end;

Note there is no need for an "else".

Edited

You can simply do it all within the update statement (ie no if construct):

update table
set ...
where ...
and exists (select 'x' from table where ...)
Community
  • 1
  • 1
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • you'll need an into clause in case of PL/SQL – Sathyajith Bhat Apr 18 '12 at 05:33
  • 1
    @Sathya the "oracle" tag wasn't on the question when I answered it - this syntax works fine for postgres and mysql – Bohemian Apr 18 '12 at 11:06
  • 3
    God I love PostgreSQL - all statements are really expressions in the functional programming sense - any statement or proc/func that returns a scalar, row/vector or table/matrix type value can be used where ever such a value would be expected, without arbitrary syntactic restrictions. – Roboprog Nov 01 '12 at 23:55
1

not so elegant but you dont need to declare any variable:

for k in (select max(1) from table where 1 = 1) loop
    update x where column = value;
end loop;
vaati
  • 162
  • 2
  • 13