Query with variables in MySQL

Question

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

After SET MySQL is expecting a list of column's names to be upgraded and the new values to be set. dev.mysql.com/doc/refman/5.7/en/update.html — SeeoX
– SeeoX, Commented Sep 22, 2020 at 14:47
@strawberry - I need the value of the selected rows. Can i get them with MySql or have I to store them into PHP variables an then run the UPDATE query with the PHP variables? — Paolo Pellizzari
– Paolo Pellizzari, Commented Sep 22, 2020 at 14:48
@SeeoX so I have to put the nested IF after the column's name, right? Like this : UPDATE bankAccount SET minimumSaving = IF(condition,IF(condition, ...) ...) — Paolo Pellizzari
– Paolo Pellizzari, Commented Sep 22, 2020 at 14:52
You cannot have two queries in one statement. You need to run them separately. I would probably go with a join on this, though. — aynber
– aynber, Commented Sep 22, 2020 at 14:52

Slava Rozhnev · Accepted Answer · 2020-09-22 15:22:54Z

1

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.';

edited Sep 22, 2020 at 15:22

answered Sep 22, 2020 at 15:01

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paolo Pellizzari Over a year ago

Thanks, it works! :D You just forgot a "," after <GREATEST(minimumSaving - monthlyValue, 0) >

Collectives™ on Stack Overflow

Query with variables in MySQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related