Complicated MySQL if statement/update

Nor · 02-26-2008, 05:07 PM

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