How to sum values in update?

[neto737]

Hi,

I'm using FluentPDO, and I haven't found anywhere how to sum a value in an update query

Like:

UPDATE `users` SET `balance` = `balance` + 100 WHERE `id` = 1;

I tried to do this way:

$query = static::getInstance()->update('users')
                ->set('`balance`', "`balance` + '{$amount}'")
                ->where('id', $userId);
        $query->execute();

But it doesn't work, is there another way to do this?

[cbornhoft]

Hey @neto737, if you haven't solved this yet, what does the code above produce?

I see you're also using a mixture of bound params and value inserts which isn't recommended. Try something like:

$query = static::getInstance()->update('users')
                ->set('balance += ?', $amount)
                ->where('id = ?', $userId);

[neto737]

Hey @neto737, if you haven't solved this yet, what does the code above produce?

I see you're also using a mixture of bound params and value inserts which isn't recommended. Try something like:

$query = static::getInstance()->update('users')
                ->set('balance += ?', $amount)
                ->where('id = ?', $userId);

Actually I tried this way, tried a lot of other ways to sum values in an update query, but nothing worked yet.

Nothing worked yet.

[neto737]

Fixed using Literal class

        $query = static::getInstance()->update('users')
                ->set([
                    'balance' => new Literal('balance + ' . $amount)
                ])
                ->where('id', $userId);

[cbornhoft]

Great to hear!

I'm going to reopen this and mark as a potential bug to fix in 3.0. There should be a way to do this without forcing a Literal value.