CODEWITHSUNDEEP
mysqljoin
brant
brant

Reputation: 537

MySQL Update Join Does not Affect ALL matching rows

I am attempting to do an UPDATE with a JOIN. I have two tables:

I want to update all rows in player_tracking for users who have fsp_f set to 1. Here is my example code:

   UPDATE player_tracking AS pt 
LEFT JOIN users AS u ON u.name = pt.user 
      SET pt.newtome = pt.newtome - 1 
   WHERE pt.first = 'Brett' 
     AND pt.last = 'Gardner' 
     AND pt.sport = 'mlb' 
     AND u.fsp_f = 1

The problem is that there are 22 rows to update, yet the UPDATE query only affects 2. Why? Is my query wrong?

Here is the data found in player_tracking pertaining to "Brett" "Gardner" "mlb": http://pastebin.com/kyf8SCy8

Upvotes: 1

Views: 656

Answers (1)

BigFatBaby
BigFatBaby

Reputation: 1510

i believe that if you change the LEFT JOIN to JOIN you will see the exact rows that get updated since you are using a field form users in the WHERE part of the statement.

so basically you are trying to check if u.fsp_f = 1 when there could be rows that do not join users and therefore will have the value as NULL.

Additionally it seems that the general layout of your query is not correct either, since you are joining on the SET statement and not in the UPDATE part - where you instructed which table to update

Upvotes: 2

Related Questions