if (select count(column) from table) > 0 then

Question

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 

Answer 1

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.

Answer 2

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 ...)