if statements in mysql?

lets say I have this query:

mysql_query("UPDATE results_tb SET num_unmarked - 1 WHERE user_id = $student AND test_id = $test AND item_id = $item")

is there a way within mysql to essentially do an IF statement which checks that if num_unmarked will be 0 after it is decremented. if it is 0 I want to update another field basically.

kinda like IF开发者_如何学运维( num_unmarked == 0 ) SET is_final = 'YES'...

just curious if there are ways to do it rather than querying and checking the value if you see where I am coming from. I am rather inexperienced with MySQL.

many thanks,

---

Sure you can

$sql="
    UPDATE results_tb
       SET num_unmarked=num_unmarked-1,
           is_final=IF( num_unmarked-1=0,'yes',is_final )
       WHERE
            user_id = {$student} 
         AND 
            test_id = {$test} 
         AND 
            item_id = {$item} 
";
mysql_query($sql)...
...

---

For completeness, there is also the CASE statement. Had you wanted to set is_final to more than one of 2 values, it would have been helpful.

UPDATE results_tb SET num_unmarked=num_unmarked-1,
           is_final=CASE WHEN num_unmarked-1=0 THEN 'yes' ELSE 'no' END
       WHERE
            user_id = {$student} 
         AND 
            test_id = {$test} 
         AND 
            item_id = {$item} 

(Yes, I just copied Itay Moav's sql and modified it)

---

Take a look at the flow control functions (Case, If,...)

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Something like:

UPDATE tablename SET is_final =IF(num_unmarked == 0,'YES','NO') where....

Here's an SO answer example.

---

UPDATE results_tb 
SET is_final = IF(num_marked = 1, 'YES', 'NO'), num_unmarked = num_unmarked - 1
WHERE user_id = $student AND test_id = $test AND item_id = $item