MySQL-5.1 is not updating some rows with a simple UPDATE query I'm trying to update a column in a table to be the same as the contents in the column of another table as part of a de-normalization process for a data warehouse project. The data is confidential, so just to cover my ass I'm going to provide an example using abstract table and column names.

I have three tables, call them A, B and C. A has about 15M rows, and is the main data cube table, and B and C are fact tables, with C being a lookup table for descriptions. Table A has a column dtype which is either 1 or 0, where 1 indicates the data is actual (derived from fact tables of actual transactions) and 0 indicates the data is theoretical (derived from projections). If I run the following statements

UPDATE A SET desc = NULL; -- Just for clarity with regards to initial state

UPDATE A, B, C SET A.desc = C.desc WHERE B.id = A.b_id AND C.id = B.desc_id AND A.dtype=1
  -- produces many warnings the last 64 of which are data truncation warnings

SELECT count(*)
FROM 
  A
  JOIN B ON B.id = A.b_id
  JOIN C ON C.id = B.desc_id
WHERE 
  A.dtype = 1
  AND A.desc &lt;&gt; C.desc


The last select statement returns 200,000+ for the count. How is this possible?

I'm using MySQL version 5.1.67-0ubuntu0.11.10.1. All the tables are MyISAM.
It turns out that there are duplicate matches for many cases of the join between A and B, and MySQL is choosing the C.desc in an undefined but consistent manner from the duplicates. When performing an UPDATE, it chooses one of the duplicates, but when SELECTing it chooses another. So selecting the ones that are different will return a result, but updating rows will indicate no changes.

Moral of the story: Don't forget to check for duplicates, because MyISAM doesn't.