MySQL Update problem.

Question

My mysql query is not updating my database correctly can some one help me fix this problem I think it has something to do with the JOIN.

Here is my mysql query.

"UPDATE users 
 SET users.last_login = NOW() 
 FROM users JOIN info ON info.user_id = users.user_id 
 WHERE (info.email = '" . $e . "' OR users.username = '" . $e . "') 
 AND users.password = '" . $sha512 . "' 
 AND users.active IS NULL"

Here is the working query before I used the JOIN.

"UPDATE users SET last_login = NOW() WHERE (email = '" . $e . "' OR username = '" . $e . "') AND password = '" . $sha512 . "' AND active IS NULL"

Answer 1

Without seeing the tables or the generated SQL, I am just guessing here, as Peeka pointed out.

My guess is that you have data in your tables that allow the (email = '" . $e . "' OR username = '" . $e . "') to work. However, this is pretty open - update any record that has an email OR a username. When you add the JOIN it's more strict - update the record that has this email OR username.

Check you second update, I bet it's not updating the record you think it is. If it is, I'd say your foreign keys are messed up and you don't have matching data in users and info. Either of these would demonstrate the JOIN is failing.

If that doesn't help - post the resulting SQL and your schema.

Answer 2

Your query syntax is wrong. The syntax for cross-table update in MySQL is somewhat
different than T-SQL. It should be as below.

 UPDATE users JOIN info ON info.user_id = users.user_id
 SET users.last_login = NOW()   
 WHERE (info.email = '" . $e . "' OR users.username = '" . $e . "') 
 AND users.password = '" . $sha512 . "' 
 AND users.active IS NULL

Check the below link -

http://blog.ookamikun.com/2008/03/mysql-update-with-join.html