CODEWITHSUNDEEP
phpsqlsql-server
TheSwiss
TheSwiss

Reputation: 47

Incrementing SQL column during UPDATE

My user_account table contains a column I created called donation_coins.

Whenever I execute the UPDATE query to set donation_coins a new value, it just deletes the old value and rewrite to the amount that has been defined in the donation process.

eg. If x had 10 donation_coins and donated again for 15 coins, his donation_coins will be 15 instead of 25.

The field type is INT with NULLS enabled.

This is the Code

connectdb($CONFIG['dbdbname'], $CONFIG['dbaddress'], $CONFIG['dbuser'], $CONFIG['dbpass']);

$query = mssql_query("SELECT * FROM user_account WHERE account = '".$account_username."'");
$row10 = mssql_fetch_array($query);

$old_coins = $row10['donation_coins'];
$transered_coins = $coin_quanity_coin;
$new_coins = $old_coins - $transered_coins;

//mssql_query("UPDATE [lin2db].[dbo].[user_account] SET [donation_coins] = '".$new_coins."' WHERE account = '".$account_username."'");
mssql_query("UPDATE [lin2db].[dbo].[user_account] SET [donation_coins] = donation_coins - ".$coin_quanity_coin." WHERE account = '".$account_username."'");

Upvotes: 0

Views: 54

Answers (3)

Martin E
Martin E

Reputation: 219

You just have to provide the new coins for the update statement

mssql_query("UPDATE [lin2db].[dbo].[user_account] SET [donation_coins] = ".$new_coins." WHERE account = '".$account_username."'");

You just calculated the new amount before your sql execution.

Cheers, Martin

Upvotes: 0

ChrisM
ChrisM

Reputation: 91

Another way to do it if > SQL Server 2005

update user_account
set donation_coins += 15
where id = @someid

Upvotes: 0

juergen d
juergen d

Reputation: 204766

Do it like this

update your_table
set x = x + 15
where id = :someId

Upvotes: 4

Related Questions