I am trying to understand MySQLs behaviour when we do an update A left join B and there are multiple records joining from B.
Below is the example.
Table A:
id val
1 100
Table B:
id val
1 -100
1 -200
I run the below update
update a1 left join b1
on a1.id = b1.id
set a1.val = b1.val;
Table A now becomes:
Table A:
id val
1 -100
Here is the fiddle: http://sqlfiddle.com/#!9/da44db/1
In this case MySQL updates the value as the first value it gets from a1 left join b1
Why is it like this? Why did the value not become -200 instead?