Alex Alex - 6 months ago 8
SQL Question

Update a table's own column with multiple INNER JOIN and UPDATE

enter image description here

Trying to use Multiple INNER JOIN while using UPDATE. Here's what I want to achieve:

Update Table1's Students and Teachers column by multiply the relative expense rate,

where Table1 & 3 match their class_Num to decides which expense rate type to use in Table 2

and match table1 & 2 match their month to get the expense rate

Here's my unable to finish's query:

SELECT Table3.expense_type as ANS
FROM
((
Table1 INNER JOIN Table3
ON Table3.Class_Num = Table1.Class_Num)
INNER JOIN Table2
ON Table2.Month = Table1.Month);


EDIT: with above Query I can get the expense type i wanted to use in table2, but how can it fits into an UPDATE statement?

Answer

It is not possible to use a text value as a column name, so when your query gets the expense_type, you cannot just make that into a column reference. So you need to make a mapping from text value to column, which you can do with the Switch function.

Something like this should work:

UPDATE    (Table1
INNER JOIN Table2
        ON Table2.Month = Table1.Month)
INNER JOIN Table3 
        ON Table3.Class_Num = Table1.Class_Num
SET        Table1.Students = Table1.Students *
               Switch(Table3.expense_type='expense1', Table2.expense1,
                      Table3.expense_type='expense2', Table2.expense2,
                      Table3.expense_type='expense3', Table2.expense3),
           Table1.Teacher  = Table1.Teacher *                   
               Switch(Table3.expense_type='expense1', Table2.expense1,
                      Table3.expense_type='expense2', Table2.expense2,
                      Table3.expense_type='expense3', Table2.expense3);