CODEWITHSUNDEEP
sqloracle-databasesql-updatecase
Eddie14
Eddie14

Reputation: 19

Using CASE WHEN To Update

I have a few tables that look like

select * from stipend
order by subjectid, stipdate;

SUBJECTID  STIPDATE     AMOUNT
---------- ----------- ----------
10011    31-oct-2021    800
10111    31-jul-2019    2000
10111    31-jul-2021    1500
20022    31-jul-2020    1200
30033    29-feb-2020    1400
40044.   31-jul-2020    1200
40044    31-jul-2021    2000
50055    31-jul-2021    2000
50055    30-sep-2021    1000


select * from subject
order by subjectid;

 SUBJECTID LNAME    FNAME      PROJID
---------- ------------ --------------------
     10011 Indy     Eva         XYZ01
     10111 Isner    Monica      XYZ04
     11011 Dupont   Marty       XYZ05
     20022 Jordan   Sam         XYZ01
     30033 Jordan   Mary        XYZ01
     40044 Belmont  Renee       XYZ02
     50055 Pissaro  Becky       XYZ02
     60066 Nadal    Becky       XYZ03
     70077 Bardot   Brigitte    XYZ03
     80088 null     Eva         XYZ03
     90099 Garnet   Larry       XYZ04

And I want to update the stipends that are in Project XYZ01 and XYZ02 by 40% using the CASE WHEN construct. Obviously the two tables are connect by stipend.subjectid = subject.subjectid and the I would use the subject.projectid to determine this, but how would I do that with CASE WHEN?

This is how I did it before

update stipend 
set amount = amount + (amount * .40) 
where subjectid in 
(select subjectid from subject 
where subject.projid = 'XYZ01' or subject.projid = 'XYZ02');

and that worked, but now I need to do the same thing but with CASE WHEN.

Upvotes: 0

Views: 162

Answers (1)

nixed.dev
nixed.dev

Reputation: 92

Eddie, below is a solution that uses the CASE WHEN statement. But, as Mureinik mentioned on the comments, your solution is better than using a CASE WHEN statement in this case.

-- using CASE WHEN
update s
set s.amount = (case when x.projid in ('XYZ01','XYZ02')
                then s.amount + (s.amount * .40) 
                else s.amount 
               end)
from stipend s
join subject x on x.subjectid = s.subjectid

Note: I did not add x.projid in ('XYZ01','XYZ02') to the where clause as that would defeat the purpose of using the CASE WHEN. So, this would run for all the rows.

Upvotes: 1

Related Questions