Update two tables in MySQL using INNER JOIN and WHERE

Question

I don't think this is possible but thought I would check if it is, and if not ask for the most efficient alternative. Objective: Update `table_1` and `table_2` using one query with an `Inner Join` and `WHERE` Currently I have: ``` UPDATE table_1 JOIN table_2 ON table_1.user_id= table_2.user_id SET table_1.value = 9, table_2.value_fan = 43 WHERE table_1.user_id = 1 AND table_2.fan_id =1 ``` This correctly updates `table_2` according to the `WHERE` condition, but all the entries are updated in `table_1` where `table_1.user_id = 1`... ignoring the condition `table_2.fan_id = 1` EDIT Sorry, I should have been more clear, I had a typo above, which is now corrected... Below is also a link to SQLFiddle http://sqlfiddle.com/#!2/58d7b/1 As I hope you can see Table_2_winnings is correctly updated with only one user, James, getting any winnings (as he has a city_id =1 and group_id =1). However all the users in table_1 in group_id = 1 are updated. Where I only want James to be updated...

Accepted Answer

```
ON table_1.city= table_2.city
```

is the only join filter in your query. Since city can come multiple times in both tables, inner join acts like some what a cross join. See this fiddle . So for getting the required columns only added one more join filter

```
and table_1.user = table_2.user;
```

So your update query will be :

```
UPDATE table_1,table_2    
SET 
    table_1.table_1_winnings = 6,
    table_2.table_2_winnings = 43
WHERE 
    table_1.city = 1 AND table_2.city_id =1
and table_1.city= table_2.city
and table_1.user = table_2.user;
```

fiddle

Update two tables in MySQL using INNER JOIN and WHERE

Solution

Problem