CODEWITHSUNDEEP
phpmysqlsqlpdo
stack
stack

Reputation: 10228

Why the number of rows affected is wrong?

Here is my query:

SELECT u.password, r.active 
FROM users u
INNER JOIN resend_pass r
ON u.id = r.user_id
WHERE r.token = 'mytoken' AND r.active = 1

The result of query above is this:

+----------+--------+
| password | active |
+----------+--------+
| mypass   | 1      |
+----------+--------+

Note: token column is unique, so the result always is one row (or zero row).

Now I convert that select statement to a update statement like this:

UPDATE users u
INNER JOIN resend_pass r
ON u.id = r.user_id
SET u.password = 'mynewpass',
    r.active   = 0
WHERE r.token  = 'mytoken' AND
      r.active = 1

And then the result will be like this:

+-----------+--------+
| password  | active |
+-----------+--------+
| mynewpass | 0      |
+-----------+--------+

Ok well, all fine. just I don't know why the number of rows affected is 2? As you see there is just one row and I've updated two columns of one row, so why row affected is 2? 

echo $stm->rowCount(); // 2

Shouldn't it be 1 ?

Upvotes: 0

Views: 104

Answers (1)

objectNotFound
objectNotFound

Reputation: 1783

SET u.password = 'mynewpass',
    r.active   = 0

thats 2 rows one in each table aliased by u and r.

Upvotes: 7

Related Questions