How can I add a value to a Row in the comic without having to add variables?

Asked

Viewed 839 times

5

Well, here’s my question:

I have the balance of a certain user Row from 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 then enter in the database.

Is there any faster way to add values to the database without always having to do this process?

Thank you.

php mysql
  • 3

    Hello, it depends on how are the data in your table, if they are along with text 100€ you will have to treat first removing the text in the querie that can be using replace, then you will have to convert to number and then make the sum, if you are without the text but contain point 100.55 can make the normal sum in the querie itself as friends reported in the answers below.

  • 1

    Frankly your question is not clear, exemplify with the current code and what you want to optimize.

5 answers

9

The fastest way I know would be using just one UPDATE:

UPDATE tbl_usuario SET vl_saldo = vl_saldo + 50 WHERE id_usuario = 123;

  • I wanted to use select, it would be better to avoid bugs in my comic book, because there are a lot of records.

  • @Gonçalo: You want to insert the added values in another table ?

  • I want to insert in the same table, but with select.

  • 4

    How so you want to insert with a select?

6


You need to be sure what kind of data that column (in this case saldo) store is numerical.

To select the results so that only 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 rows:

UPDATE tabela SET saldo = saldo + 50;

Or update in another column in all rows:

UPDATE tabela SET saldo2 = saldo1 + 50;

To specify that you want these actions to be imposed on certain rows in the table, specify which lines complement any of the above examples (delete the ; of the end) with:

... WHERE id = 3;

In this case I only want the command to be executed (query) in row(s) (s) with column id equal to 3

1

It would basically look something like this. Note that you may need to consist of the single fields or primary keys of the table and name the fields in the same order as the table creation. For better detail please post table columns

insert into tabela select campo1,campo2...,valor+50 from tabela where usuario = idUsuario

1

  SELECT saldo FROM tabela;

    while (...) 
     {
      saldo =saldo +50;
     }

1

I’d do a Stored Procedure

in your mysql command (I think you are using mysql)

the first line of 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 trades the delimeter back to ";"

to use the query now 

 CALL addSaldo("nomeDoCliente", valorASerAdicionado);

using this method you will have better efficiency, better maintenance and more security.

if you want to do outside of mysql command (easy maintenance)

1) create an addSaldo.sql file

2) put the above code inside this file and save

3) run the command (Linux)

mysql -u root -pSenhaDoRoot NomeDaDatabase < addSaldo.sql

ready , if you want to make future change and only change in the file , drop in the previous and run this command.

as I do not know the structure of your table has no way to help you in the format of queries.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.