Mysql - Update on every Row

Asked

Viewed 255 times

2

Guys, I’ve been trying to put together a line of code for a while, but I never get the desired result, ever arrived in 3 codes but none of the 3 executes the code for each result found. It is running the code only for the last result of when the condition is true ... Does anyone know how to put this all inside for example a FOR for each Row result found ?

Code 1 - It does change, but if it finds 2 or more results with id_user = id_users it only performs the code for the last valid result.

UPDATE tb_loja, tb_usuarios 
set carteira = carteira + valor * 0.3, finalizado = "sim"  
where data_inicial = CURDATE() and id_usuario = user_id and finalizado = "nao";

Code 2 - Exactly the same problem as the first code

update tb_usuarios
inner join tb_loja us on us.id_usuario = tb_usuarios.user_id
set tb_usuarios.carteira = tb_usuarios.carteira + us.valor * 0.3, us.finalizado = "sim"
where us.data_inicial = CURDATE() and us.finalizado = "nao";

Code 3 - This is just a SELECT I’ve assembled to see if you’re really selecting everything right (and yes, it’s).

SELECT id_usuario, valor, carteira, user_id 
from tb_loja, tb_usuarios 
where data_inicial = CURDATE() and id_usuario = user_id;

Can anyone give me a help on this? I don’t know how to make this code work according to each item found. Thanks

mysql sql phpmyadmin

  • Pablo all right? Just to try to understand better, what you need to do (in terms of logic) and what result you expect?

  • Good afternoon fall ! Well, I have 2 tables: store and users. I need to check in the store table if the date filled in the "Start Date" column is equal to today, if it is equal to today then I take the "wallet" column that is in my users table and update its value according to x% of my "value" column of the store table. To perfectly match the two tables I am using "id_usuario = user_id" because when this condition is true, it means I am talking about the same person in different tables

  • To better understand: The table "store" refers to transactions, ie, ALL the lines you have in tb_store is some user who is registered in tb_usuarios, I just need to update my user’s wallet when the initial date corresponds to today... I could tell?

  • Hello @Pabloabreu. Edit your question and post the information you have just given in comment. It is easier to read.

1 answer

2


Good morning, @Pabloabreu from what I read in the comments of your question you want to update in the table users with data from your table shop, for this you want to perform an update with select:

UPDATE
    tb_usuarios
SET
    tb_usuarios.carteira = tb_usuarios.carteira + tb_loja.valor * 0.3, tb_loja.finalizado = "sim"
FROM
    tb_usuarios
    inner join tb_loja on tb_loja.id_usuario = tb_usuarios.user_id
WHERE
    tb_loja.data_inicial = CURDATE() and tb_loja.finalizado = "nao";

Perform the above SQL test if it solves your problem.

Browser other questions tagged mysql sql phpmyadmin

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