Well my question is like this:
I have the row balance of a given user of my database table that has 100 €, and I wanted to add + 50 €, as I can do without having to create a variable with his current balance, plus a variable with the balance to add, plus a variable with the sum of the two values and insert it into the database.
Is there a faster way to add values to the database without having to always do this?
Thank you.
You have to make sure that the data type that this column (in this case saldo ) stores is numeric.
To select the results so that only the data with + 50 is displayed. You can do:
SELECT saldo + 50 as saldo FROM tabela;
But if you really want to update the column on all lines:
UPDATE tabela SET saldo = saldo + 50;
Or update another column in all rows:
UPDATE tabela SET saldo2 = saldo1 + 50;
To specify that you want these actions to be enforced on certain table rows, specify which rows complement any of the above examples (delete ; from the end) with:
... WHERE id = 3;
In this case, I want the command to be executed ( query ) on the row (s) whose id column is 3
The quickest way I know would be to use just UPDATE :
UPDATE tbl_usuario SET vl_saldo = vl_saldo + 50 WHERE id_usuario = 123;
SELECT saldo FROM tabela;
while (...)
{
saldo =saldo +50;
}
Basically it would look something like this. Note that you may have to consist of the unique fields or primary keys of the table and name the fields in the same order as the table creation. For better detailing please post the columns of the table
insert into tabela select campo1,campo2...,valor+50 from tabela where usuario = idUsuario
I would do a Stored Procedure
in your mySQL command (I think you are using mySQL)
the first line swaps the sql delimiter
DELIMITER $$
CREATE PROCEDURE IF NOT EXISTS addSaldo(IN costumerName VARCHAR(255), IN valor INT)
BEGIN
Select campo seus queries aqui .......
adicione usando variable valor ......
WHERE ...... = costumerName;
END $$
DELIMITER ;
Last line swaps the delimeter back to ";"
To use now use the query
CALL addSaldo("nomeDoCliente", valorASerAdicionado);
Using this method you will have better efficiency, better maintenance and more security.
If you want to do outside mySQL command (easy maintenance)
1) create a file addSaldo.sql
2) put the above code into this file and save
3) run the command (Linux)
mysql -u root -pSenhaDoRoot NomeDaDatabase < addSaldo.sql
Now, if you want to make a future change and just change the file, make a drop in the procedure and run this command.
As I do not know the structure of your table can not help you in the format of the queries.