SQL & PL/SQL

Scuze me, I forgott the code

update expertise_areas
set expertise_level =
case (when expertise_level='user') then (expertise_level='appretissance')
end case

update expertise_areas
set expertise_level =
   case expertise_level when 'user' then 'appretissance'
                                  else expertise_level
   end case                                                                                                                                                                                                                                                                                                                                                                        

Scuze me, I forgott the code

What is the error you are receiving??? Isn't it worth to post the actual error.

Your syntax is incorrect and I think the logic is also incorrect at the same time.

Without the Where clause you will end up updating all the non-matching rows to NULL.

SQL> update expertise_areas
  2  set expertise_level =
  3  case (when expertise_level='user') then (expertise_level='appretissance')
  4  end case ;
case (when expertise_level='user') then (expertise_level='appretissance')
           *
ERROR at line 3:
ORA-00907: missing right parenthesis

SQL> select * from expertise_areas;

EXPERTISE_LEVEL
------------------------------
user
test
appretissance

SQL> update expertise_areas
  2  set expertise_level =
  3  case when expertise_level='user' then 'appretissance'
  4  end ;

3 rows updated.

SQL> select * from expertise_areas;

EXPERTISE_LEVEL
------------------------------
appretissance

If you want to update all the values of "expertise_level = 'user'" to "appretissance" then you should be using something like this:

SQL> rollback;

Rollback complete.

SQL> update expertise_areas
  2  set expertise_level = 'appretissance'
  3  where expertise_level='user' ;

1 row updated.

SQL> select * from expertise_areas;

EXPERTISE_LEVEL
------------------------------
appretissance
test
appretissance

SQL>

update expertise_areas
set expertise_level =
case expertise_level when 'user' then
'appretissance'
else
expertise_level
end case

Hi Jeneesh,

Did you try to execute the Update statement you posted?

SQL> update expertise_areas
  2  set expertise_level =
  3     case expertise_level when 'user' then 'appretissance'
  4                                    else expertise_level
  5     end case;
   end case
       *
ERROR at line 5:
ORA-00933: SQL command not properly ended

Sorry, No.I just copy-pasted the code and made necessary changes.

@op:

change "end case" to "end"

thank you all of you. It works now.

and I need one more thing if you can:
I have now the following:
1 update expertise_areas
2 set expertise_level =
3 case when expertise_level='user' then 'appretissance'
4 else
5 case when expertise_level='tehnician' then 'middle'
6 else
7 case when expertise_level='manager' then 'expert'
8 else expertise_level;
9* end;
SQL> /
else expertise_level;
*
ERROR at line 8:
ORA-00911: invalid character

What is wrong here?

Thank you

> 8 else expertise_level;

Please don't post simple syntax errors in the forum. Look at line 8.

Ok. I looked and droped ";". It still does not work. I have now
8 else expertise_level
9 end case;
10 end

and the error
ERROR at line 9:
ORA-00905: missing keyword

Can you help me?
Thank you

This is too rubbish...

Before posting, you have to carefully read your code atleast ONCE!

I looked and I do not know what is wrong.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/toc.htm

Look up CASE.

Just take a look at this nested case syntax based on emp table of SCOTT schema......

SQL> select ename,sal from emp order by 1;
 
ENAME            SAL
---------- ---------
ALLEN        1600,00
BLAKE        2850,00
CLARK        2450,00
FORD         3000,00
JAMES         950,00
JONES        2975,00
KING         5000,00
MARTIN       1250,00
MILLER       1300,00
SMITH         800,00
TURNER       1500,00
WARD         1250,00
 
12 rows selected
 
SQL> 
SQL> update emp set sal=case when sal<=1000 then 0 else case when sal>=1000 and sal<=1500 then 1
  2           else case when sal>1500 and sal<=2500 then 2 end end end
  3  /
 
12 rows updated
 
SQL> 
SQL> select ename,sal from emp order by 1;
 
ENAME            SAL
---------- ---------
ALLEN           2,00
BLAKE      
CLARK           2,00
FORD       
JAMES           0,00
JONES      
KING       
MARTIN          1,00
MILLER          1,00
SMITH           0,00
TURNER          1,00
WARD            1,00
 
12 rows selected

Greetings...
Sim

thank you. Now it works