CODEWITHSUNDEEP
phpmysql
Paolo Pellizzari
Paolo Pellizzari

Reputation: 11

Query with variables in MySQL

I'm coding in PHP and I'm having some troubles with variables in MySQL query. In particular i'm trying to pass a value from a SELECT query to an UPDATE query.

The query that I'm running is:

$queryUpddateMinimumSaving = '
    SELECT 
        @monthlyVal:= monthlyValue,
        @fixedElementType:= codElementType,
        @username:= username
    FROM fixedElement
    WHERE codElement = '.$codFixedElement.';

    UPDATE bankAccount SET
    IF(
        @fixedElementType = 1,
        minimumSaving = IF((minimumSaving - @monthlyVal) >=0,
            minimumSaving - @monthlyVal,
            0
        )
        minimunSaving = minimumSaving + @monthlyVal
    )
    WHERE username = @username
;';

Even running the query in PHPMyAdmin the query doesn't work. MySql shows me this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF( @fixedElementType = 1, minimumSaving = IF((minimumSaving -' at line 2

But I don't understand what I'm doing wrong ^^" I'm looking in MySql doc and the query looks right

May you help me? Thanks

Upvotes: 1

Views: 56

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can use single query for this problem:

UPDATE bankAccount 
JOIN fixedElement ON fixedElement.username = bankAccount.username
SET
    minimunSaving = IF(
        codElementType = 1,
        GREATEST(minimumSaving - monthlyValue, 0),
        minimumSaving + monthlyValue
    )
WHERE codElement = '.$codFixedElement.';

Upvotes: 1

Related Questions