mysql insert nested select from other db truncates double values

Question

I have a table in one database, call this db x. I have another database, call it y. I want to copy data from x.some_table to y.some_table. I don't want to do an exact copy of the table, because some columns don't make sense in database b. I use the following query:

INSERT INTO y.some_table (a_field) SELECT a_field FROM x.some_table;

a_filed in both tables is defined as DOULBE(17,0). If i run this: USE y; SELECT a_field FROM x;

Then I get output with full values --no floating-point truncation. However, if after insertion using the first query I showed, I get nothing but whole numbers in y's some_table.a_field. The floating-point remainders are truncated.

What am I doing wrong? Thanks.

Answer 1

Are you sure that the column is defined as DOUBLE(17,0) in both tables? Doesn't that specify 17 total digits with 0 after the decimal? If so you're select from table x should also have 0 decimal places. If its defined differently in x say DOUBLE(17,6) and you are trying to insert it into DOUBLE(17,0) then the decimals will be truncated I believe.

Answer 2

Not sure what is causing truncation .. you can make sure that you properly set the floating type .. if you think your table definition is OK, you can create a script to test it for example, in PHP you could do something like -

$sql = "SELECT your_select_field FROM your_table";
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result)) {
   $sql_ins = "INSERT INTO your_insert_table SET your_field = '".$row['your_select_field']."' ";
   $res_ins = mysql_query($sql_ins);
}