Update query INNER JOIN mysqli

Question

If I want to make an update query using INNER JOIN, how do I do in this case:

I have a users table & user_settings table. users contains column id and user_settings contains column userid. I want to do like:

UPDATE users_settings SET commets = 0 WHERE email = "$email";

But user_settings does not contain email, so I want to pick the email from the users table where the id is userid.

Answer 1

Try this:

UPDATE user_settings s 
INNER JOIN users u ON s.userid=u.id
SET s.commets = 0 WHERE u.email = "$email";

Note: There should be no users with the same e-mail in these cases.

Answer 2

You can use this query. it will work

UPDATE users_settings SET commets = 0 WHERE userid
IN (SELECT id FROM users WHERE email = "$email");

Answer 3

The following will be helpful

UPDATE US SET US.Comments = 0
FROM User_settings US,  Users U
WHERE US.Userid = U.id
AND U.email = 'emailid' 

Answer 4

JOINs are slightly better in terms of performance than SUB QUERIES. Reference here.

So instead of using this -

UPDATE users_settings SET commets = 0 WHERE userid
IN (SELECT id FROM users WHERE email = "$email");

I would suggest this using INNER JOIN -

UPDATE users_settings 
SET commets = 0 
FROM user_settings 
INNER JOIN users 
on users.id = user_settings.userid   --JOIN CONDITION
WHERE users.email = "$email" ;       --SELECTION/SLICING CRITERION

Hope this helps.