How to update the right column?

[syafia]

hye!

Im a newbie in MySQL and Php, i got a problem to update column in db. Here is the issue:

i have 2 tables named 'apply' and 'balance'.

Table: apply
userId  |  leaveType    |daysApply
1       |     Annual    |         3
2       |    Medical    |        1
 
Table: balance
userId  |  Annual   | Medical
1       |     14       |     3
2       |    14       |    3

When user apply for leave, the system will deduct the value from table:balance based on apply.leaveType = balance.@column name. My problem is i dont know how to update the column value based on the apply.leaveType value from other table.

i try to do this but it doesnt make sense:

UPDATE  `balance`, `apply` 
SET     `balance.Annual`=`balance.Annual`-`apply.daysApply`, 
        `balance.Medical`=`balance.Medical`-`apply.daysApply` 
WHERE   `balance.Annual`=`apply.leaveType` 
AND     `balance.Medical`=`apply.leaveType` 
AND     `balance.userId`=`apply.userId`

Hope someone can help me. Any helps are appreciated.

[code green]

I set out writing an UPDATE JOIN statement before I realised your database design seems to be wrong
The balance table should have multiple entries for each user.
ie one medical and one annual

Table: balance
userId  leavetype         days
1          Annual             14           
1          Medical             3
2          Annual             14           
2          Medical             3

Taking this further for better performance and maintenance,
I would have a third table of leave types

Table: type_codes
code   leavetype         
1          Annual                       
2         Medical       

and balance table

Table: balance
userId  leavetype         days
1          1                    14           
1          2                     3
2          1                    14           
2          2                     3

Now the query should almost write itself

[Atli]

Hey.

It occurs to me, that the balance table in the original post doesn't need to be a real table. You cold create a View for it.

That is; the columns of the balance table could be generated by doing a SELECT on the apply table. Thus, the balance table is redundant. A SELECT query - or a View - could take it's place.

For example, if we use code_green's balance table - which is, by the way, named incorrectly; it takes the place of the apply table, not the balance table - as the apply table, we could create the following View:

CREATE VIEW `balance` AS
SELECT
    o.`userID`,
    (   SELECT  SUM(i.`days`)
        FROM    `apply` AS i
        WHERE   i.`userID` = o.`userID`
        AND     i.`leavetype` = 1
    ) AS 'Annual',
    (   SELECT  SUM(i.`days`)
        FROM    `apply` AS i
        WHERE   i.`userID` = o.`userID`
        AND     i.`leavetype` = 2
    ) AS 'Medical'
FROM    `apply` AS o
GROUP BY o.`userID`

Which would imitate a balance table, allowing you to simply call it as a normal table:

SELECT * FROM `balance`;

+--------+--------+---------+
| userID | Annual | Medical |
+--------+--------+---------+
|      1 |      3 |      16 | 
|      2 |      7 |       9 | 
+--------+--------+---------+

Also, just to point this out.
In your query you did this:

WHERE `balance.Annual`=`apply.leaveType` 

You can not group table and column names together inside a single back-tic (`) group. That should have looked like this:

WHERE `balance`.`Annual`=`apply`.`leaveType` 

The back-tics are meant to encapsulate a single database, table or column name, not a list of names.

[syafia]

ok..i got the solution at last. Thanks to code green for realizing me that something going wrong with my db table. I've alter back my db table and got the solution to update the balance table.

And thanks to atli for giving me new knowledge about using VIEW in Mysql.

Both helps are really appreaciated ^_^