Multiple updates with Single Update statement 1

[barnard90]

Hi

I need to update the salary an employee table
It should be a single UPDATE statement such that , I can update all the jobs in a single update

If the job is 'CLERK' then
Sal = Sal + 200

If the job is 'OFFICER' then
Sal = Sal + 500

If the job is 'MANAGER' then
Sal = Sal + 500

Ideally, only for one job the way I would write is

UPDATE emp SET
Sal = Sal + 200
WHERE job = 'CLERK'.

Please suggest me an UPDATE for multiple updates . I might not need a PL/SQL procedure

thanks

 

[feherke]

Hi

Not much nicer :

[b]update[/b] emp [b]set[/b] sal=sal+[b]case[/b]
  [b]when[/b] job=[i]'CLERK'[/i] [b]then[/b] 200
  [b]when[/b] job=[i]'OFFICER'[/i] [b]then[/b] 500
  [b]when[/b] job=[i]'MANAGER'[/i] [b]then[/b] 500
  [b]else[/b] 0
[b]end where[/b] job [b]in[/b] ([i]'CLERK'[/i],[i]'OFFICER'[/i],[i]'MANAGER'[/i])

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

More Oracle styled :

[b]update[/b] emp [b]set[/b] sal=sal+decode(job,[i]'CLERK'[/i],200,[i]'OFFICER'[/i],500,[i]'MANAGER'[/i],500,0) [b]where[/b] job [b]in[/b] ([i]'CLERK'[/i],[i]'OFFICER'[/i],[i]'MANAGER'[/i])

Note, that in both cases works without hte [tt]where[/tt] clause too, updating the other records with +0.

Feherke.

http://rootshell.be/~feherke/