UPDATE WITH SELECT AS A CONDITION

Asked

Viewed 450 times

2

I have an app that sends an email to the user to confirm their email as soon as they sign up.

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

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

0 if the user has not yet confirmed his email.

1 if the user has already confirmed his email.

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

I did so, but this way it puts as if all users had already confirmed your 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, ie, logado=1.

If anyone can help.

Tabela Usuários Tabela Login

mysql database
  • 1

    Put more information about your database, post the structure

  • 1

    You are not specifying which user to switch to "1", you need to specify which user id to change. Ex: UPDATE usuarios SET confirmado = '1' WHERE usuarios.id = 1

  • you need to pass the user code as a condition for Update

  • 1

    I think you need to add where logado = 1. See the answer already given and confirm, which condition you need, so the answer will be edited and you can accept.

4 answers

2

The field usuario of the second table is the id of user?

If so, you can do so:

UPDATE usuarios SET confirmado = '1' where usuario in (select id from LOGIN where logado = 1);

0

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

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

Without the WHERE in his UPDATE, all records in that table will be updated. It is recommended, inclusive, that there be no UPDATE directly in the table without any condition WHERE (except in specific cases);

  • Wouldn’t I have to do an Inner Join? For having to take data from another table?

  • Depends, you will need to have the id of that user who is logged in. For example (according to your tables): UPDATE usuarios SET confirmado = '1' WHERE usuario = '19'". However, you should already have that user’s id in your code and pass it to your SQL via a variable. But if you don’t want to use a variable with the ID, you can do a SUBSELECT in the table LOGIN users logged in, however, will update all users who are logged in.

  • 1

    The problem can be solved with an in: UPDATE usuarios SET confirmado = '1' WHERE usuario in (SELECT usuario FROM login WHERE logado = '1'). @Tiffany, see if you can fix it...

  • @Melissa not yet :(

  • 1

    As has already been said, before applying the update, you need to make a select at the base, probably using the registered email (it is unique to each user, right?), to recover the logged in user at the time. Recovering this user, you will have the id of it, which is what you should go through, so where of your update.

  • I did so but nothing happens: if(data.content.confirmed == 1){ //returns 1 var emailTeste = data.content.email; // returns the right email update = "UPDATE users SET confirmed ='1' WHERE email = 'emailTeste'"; but the confirmed continues 0.. does not update.

  • In this case it may be that he is not entering into your IF. Have you tested whether the UPDATE is working outside of IF? Or if the value of the emailThis is correct?

  • It’s all right. I gave an Alert on each item and it appeared the data correctly. I don’t know what can be..

  • Is there any SQL error then? If not, try using LIKE instead of = to compare with the email. If it still doesn’t work, you would need to see your code and check your SQL as it is running, with the email being used in the comparison.

Show 4 more comments

0

I’ll ask you again! The user field of the second table is whose foreign key, is it from the id field of the login table? If not, your table structure is wrong.

Your login and users table should have some connection between them.

Table users:

+------------+-------+-------+------------+
| usuario_id | nome  | senha | confirmado | 
+------------+-------+-------+------------+
| 1          | Maria | 1568  | 1          |
+------------+-------+-------+------------+
| 2          | João  | 1234  | 0          |
+------------+-------+-------+------------+

login table:

+----------+--------+------------+
| login_id | logado | usuario_id |
+----------+--------+------------+
| 58       | 1      | 1          |
+----------+--------+------------+
| 59       | 0      | 2          |
+----------+--------+------------+

Done this is just apply the update I posted above:

UPDATE usuarios SET confirmado = '1' WHERE usuario_id IN(SELECT usuario_id FROM login WHERE logado = 1)

When you log in, do you not enter the person’s email in the form? You can update this way. As long as your structure is correct:

UPDATE usuarios SET confirmado = '1' WHERE usuario_id = (SELECT usuario_id FROM login WHERE email= '[email protected]')

0

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

try and tell me.

update usuario a Join login b on a.id_do_usuario = b.id_do_usuario set a.confirmed = 1 Where b.logged in = 1;

VLW hug

Browser other questions tagged mysql database

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