Using CASE WHEN across tables-CodePudding

I have a few tables that look like

SQL> select * from stipend
2 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


SQL> select * from subject
  2  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?

CodePudding user response：

Have you tried this?

SELECT 
SUBJECTID
,PROJID
,CASE WHEN ProjectID in ('XYZ01','XYZ02') THEN AMOUNT * 1.4 ELSE AMOUNT END as AMOUNT
FROM subject sb
LEFT JOIN  stipend st ON st.SUBJECTID = sb.SUBJECTID

CodePudding user response：

You may try this. Here I have used Stipend table as LEFT Table, you may alter this:

SELECT 
st.SUBJECTID, sb.PROJID, Amount,
  CASE WHEN ProjID in ('XYZ01','XYZ02') THEN AMOUNT * 1.4 
       ELSE AMOUNT 
  END as Updated_AMOUNT
FROM stipend st 
LEFT JOIN  subject sb
   ON st.SUBJECTID = sb.SUBJECTID

Note: Using subject as left table may result in different level. Try them in Demo link below.

Demo

CodePudding user response：

Pls check this. I have no data, so haven't tested it.

MySQL:

UPDATE
  stipend st
  join subject sub on st.SUBJECTID = sub.SUBJECTID
SET
  AMOUNT = CASE WHEN 
  sub.ProjectID in ('XYZ01', 'XYZ02') 
  THEN AMOUNT * 1.4 
  ELSE AMOUNT 
  END

Oracle

I guess SQL Plus has similar syntax of Oracle DB, you can try this

Note: This syntax is converted online by https://www.sqlines.com/online . Pls check it

UPDATE
  stipend st
  join subject sub on st.SUBJECTID = sub.SUBJECTID
  AMOUNT := CASE WHEN 
  sub.ProjectID in ('XYZ01', 'XYZ02') 
  THEN AMOUNT * 1.4 
  ELSE AMOUNT 
  END

CodePudding user response：

Better to go for a regular join as left join would pull duplicate values in your result.


UPDATE STPIEND SET AMOUNT = CASE WHEN T1.AMOUNT IN ('XYZ01','XYZ02') THEN T1.AMOUNT * 1.4
ELSE T1.AMOUNT
END AS UPDATED_AMOUNT
FROM STIPEND T1 JOIN SUBJECT T2 ON T1.SUBJETID = T2.SUBJETID