if select count column from table 0 then

Question

I need to check a condition  i e   if  condition  gt  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  gt  o then   update table else do    not update

User · Accepted Answer

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.

User · Answer

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   gt  0 then      update table   end if  end   Note there is no need for an   quot else quot   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

User · Answer

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

[oracle] if (select count(column) from table) > 0 then

Examples related to oracle

Examples related to plsql