UPDATE WITH CASE SQL

Question

UPDATE WITH CASE SQL

Navigation

#1 by (5 votes)

1

Is there any way I can use more than one column of the same table in a case statement in an update? for example, I have this table with these 3 lines

create table produto2(
codigo int primary key,
categoria varchar(30),
valor number(7,2));

insert into produto2 values (1001,'A',7.55);
insert into produto2 values (1002,'B',5.95);
insert into produto2 values (1003,'C',3.45);

I'm trying to use a case with the column category and value to increase the value by 5.10 and 15 percent when the category is A, B, C

 update produto2 set valor = case when categoria = 'A' then valor*(valor*5/100)
                             case when categoria = 'B' then valor*(valor*10/100)
                             case when categoria = 'B' then valor*(valor*15/100)
                             end;

But I'm not having success with just a PL / SQL that I've created, but as you can see it is much more laborious, I wanted to try to simplify it with just the case.

declare
cursor c_prod is select * from produto;
v_prod c_prod%rowtype;
begin
open c_prod;
loop
fetch c_prod into v_prod;
exit when c_prod%notfound;
end loop;
close c_prod;
if v_prod.categoria = 'A' then
update produto set valor = valor+(valor*5/100) where categoria = 'A';
elsif v_prod.categoria = 'B' then
update produto set valor = valor+(valor*10/100) where categoria = 'B';
elsif v_prod.categoria = 'C' then
update produto set valor = valor+(valor*15/100) where categoria = 'C';
end if;
end;

But anyway, is there anyway?

sql oracle pl-sql

asked by anonymous 16.05.2018 / 02:03

1 answer

Image occupy entire column Catch only part of a url

score 5 · Answer 1

You have a syntax error in your Update, you should use only one case and each condition inside a block when

update produto2
   set valor = case
                 when categoria = 'A' then
                  valor * (valor * 5 / 100)
                 when categoria = 'B' then
                  valor * (valor * 10 / 100)
                 when categoria = 'C' then
                  valor * (valor * 15 / 100)
               end;