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.