CODEWITHSUNDEEP
plsqlsql-updatecursor
F.Bruno
F.Bruno

Reputation: 143

PL/SQL using IF statement inside Update

Was wondering if something like this:

begin
    for C2 in cursor_sal loop
        if something then
            update emp
            set name = George
            where ID = 1
        elsif something2 then
            update emp
            set name = Steve
            where ID = 4
        end if
    end loop;
end;

Could be turned into something like this or anything similar:

begin
    for C2 in cursor_sal loop
        update emp
        if something then
            set name = George
        elsif something2 then
            set name = Steve
        end if
        where current of C2
    end loop;
end;

Or is this just not possible and an I stuck with the first example?

Upvotes: 1

Views: 9626

Answers (2)

Shaun Peterson
Shaun Peterson

Reputation: 1790

Best way I know of doing this is using a case statement as per the example below. Code is untested but should be enough for you to go ahead on.

begin
    for C2 in cursor_sal loop
        update emp
        set name = case
                     when something     then 'George'
                     when somethingelse then 'something2'
                     else 'somthing 3'
                   end
        where current of C2
    end loop;
end;

Upvotes: 4

Durga Viswanath Gadiraju
Durga Viswanath Gadiraju

Reputation: 3956

You can use case statement in update statement like this

update emp
  set name = case when something then 'George'
                  when something2 then 'Steve'
             end;

Also if it is equal condition you can use decode function as well.

update is sql statement and if statement is pl/sql construct. You can use sql statements in pl/sql but not pl/sql constructs in sql.

Upvotes: 1

Related Questions