TalkPHP - Complicated MySQL if statement/update

TalkPHP (http://www.talkphp.com/forums.php)

- MySQL & Databases (http://www.talkphp.com/mysql-databases/)

- - Complicated MySQL if statement/update (http://www.talkphp.com/mysql-databases/2342-complicated-mysql-if-statement-update.html)

Nor	02-26-2008 05:07 PM

Complicated MySQL if statement/update

Right I'm trying to do the following:

(pseudo)

Code:

update tablename set `cash` = ( IF cash - # > 0 SET `cash` = `cash` - # ELSE SET `cash` = 0) WHERE uid = 1

How do I do this?

Someone said to use this yet I think its not how I think its supposed to be setup:

Code:

UPDATE `mem` SET `uM` = (

    SELECT IF ((SELECT (`uM` - %d) FROM `mem` WHERE `uid` = %d) > 0), 

                         (SELECT (`uM` - %d) FROM `mem` WHERE `uid` = $d), 

                          0

                          )

Wildhoney

02-26-2008 05:25 PM

Something like the following? The @fValue variable is the amount to deduct.

sql Code:

UPDATE
    cash,
    (
        SELECT
            @fValue:=1.99,
            @fCash:=(cash - @fValue),
            @fCash:=IF(@fCash > 0, cash - @fValue, 0)
        FROM
            cash
        WHERE
            id = 1
    )
    AS fCash
SET
    cash = @fCash
WHERE
    id = 1

Salathe

02-26-2008 05:32 PM

Substitute the necessary values (denoted by %d) however you want to do it (sprintf, etc.) and of course the table/column names!

SQL Code:

UPDATE 
    cash_table
SET 
    cash_value = IF (cash_value - %d > 0, cash_value - %d, 0)
WHERE
    cash_id = %d
;

Nor	02-26-2008 06:23 PM

Ah thanks I'll give them a try when I get home the guys at phpfreaks just suck...@support

All times are GMT. The time now is 02:56 AM.