Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 <> 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.

share|improve this question

closed as too localized by Derek Downey, Max Vernon, Mark Storey-Smith, dezso, RolandoMySQLDBA Apr 13 '13 at 5:05

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.