Got a little issue here. I'm periodically making an automated update of currencies in a MySQL table. Part of the generated query is :
UPDATE ara_curr SET
curr_xchange=REPLACE(CONVERT(CONVERT('1.60739',DECIMAL(9,5))/CONVERT('10000',DECIMAL(9,5)),DECIMAL(9,5)),'.',','),
curr_rev_xchange=REPLACE(CONVERT('6221.27',DECIMAL(9,5)),'.',',')
WHERE curr_name='IDR';
I get the values from a online service. However this query returns
Out of range value for column (null) at row 1
However if I break it down the conversions return these
REPLACE(CONVERT('6221.27',DECIMAL(9,5)),'.',',') = 6221,27000
CONVERT('1.60739',DECIMAL(9,5)) = 1,60739
CONVERT('10000',DECIMAL(9,5)) = 9999,99999
CONVERT(CONVERT('1.60739',DECIMAL(9,5))/CONVERT('10000',DECIMAL(9,5)),DECIMAL(9,5)) = 0,00016
And if I try to run the query directly with those values e.g.
UPDATE ara_curr SET curr_xchange='0,00016', curr_rev_xchange='6221,27000' WHERE curr_name='IDR';
It runs perfectly OK!
Any ideas about this?
What datatypes are curr_xchange and curr_rev_xchange?
If i run your query it returns the result as binary data. Maybe you can try this modification with a CAST around the results.
UPDATE ara_curr SET
curr_xchange=CAST(REPLACE(CONVERT(CONVERT('1.60739',DECIMAL(9,5))/CONVERT('10000',DECIMAL(9,5)),DECIMAL(9,5)),'.',',') AS CHAR),
curr_rev_xchange=CAST(REPLACE(CONVERT('6221.27',DECIMAL(9,5)),'.',',') AS CHAR)
WHERE curr_name='IDR';
Try using CAST instead of CONVERT:
UPDATE ara_curr
SET REPLACE(CAST(CAST('1.60739' AS DECIMAL(9, 5)) / CAST('10000' AS DECIMAL(9, 5)) AS DECIMAL(9, 5)), '.', ',') AS curr_xchange
, REPLACE(CAST('6221.27' AS DECIMAL(9, 5)), '.', ',') AS curr_rev_xchange
WHERE curr_name = 'IDR';
Also, you may run into some trouble casting 10000
as a DECIMAL(9, 5)
as it exceeds the allowed range. You may want to cast that to DECIMAL(10, 5)
instead.