UPDATE WITH SELECT AS CONDITION

Question

UPDATE WITH SELECT AS CONDITION

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I have an application that sends an email to the user to confirm their email when they register.

Then I have two tables in my internal database, being USUÁRIOS and LOGIN .

In the USUÁRIOS table I have the field confirmed, which receives 0 or 1 :

0 if the user has not yet verified your email.

1 if the user has already confirmed his email.

In the LOGIN table, I have the logged in field, which is the user who is logged in at the moment. Let's say he has already confirmed his email. Therefore, I need to select the user that is logged in and update the field confirmed in the users table to 1 .

I did that, but that way it sounds like all the users have already confirmed their email:

update = "UPDATE usuarios SET confirmado = '1'";

db = getDataBase();
db.transaction (function (tx){
    tx.executeSql (update);
    });

In short, I want it to update only the user who is logged in, that is, logado=1 .

If anyone can help.

mysql

asked by anonymous 13.08.2018 / 20:25

2 answers

Spring JPA - Procedure call with sqlserver dialect Doubt - JavaScript [closed]

score 0 · Answer 1

You need to add the WHERE clause to your SQL to update only the user you want. Something like:

update = "UPDATE usuarios SET confirmado = '1' WHERE id_usuario = '<usuario_logado>'";

Without WHERE in its UPDATE , all records in that table will be updated. It is also recommended that a UPDATE not be made directly in the table without any WHERE condition (except specific cases);

score 0 · Answer 2

You have to do an update with join between the login table and the user table on id of the table user = id of the login table (id of the user) ai you give a set a.confirmed = 1 where the b.logado ( which is the login table) is = 1.

Try and talk to me.

update user to join login b on a.id_do_usuario = b.id_do_us set a.confirmed = 1 where b.logged = 1;

VLW hug