MYSQL - Truncated incorrect DOUBLE value: '-'

0
votes

This UPDATE fails with the error "Truncated incorrect DOUBLE value: '-'" and I cannot figure out why.

UPDATE psych
SET pdqp_adm=CAST((CAST(pdqt_adm AS SIGNED)) - (CAST(pdqf_adm AS SIGNED)) AS CHAR)
WHERE pdqt_adm>0 
    AND pdqt_adm IS NOT NULL 
    AND pdqf_adm>0 
    AND pdqf_adm IS NOT NULL 
    AND pdqt_adm>=pdqf_adm

All of the columns used here (pdqp_adm, pdqt_adm, pdqf_adm) are VARCHAR(6). I can do this query and the calculation works just fine:

SELECT CAST((CAST(pdqt_adm AS SIGNED)) - (CAST(pdqf_adm AS SIGNED)) AS CHAR)
FROM psych
WHERE pdqt_adm>0 
    AND pdqt_adm IS NOT NULL 
    AND pdqf_adm>0 
    AND pdqf_adm IS NOT NULL 
    AND pdqt_adm>=pdqf_adm
2
mysql
This is curious. Can you reproduce it on SQL Fiddle? - Gordon Linoff
I'm trying to reproduce it on SQL Fiddle, but so far, no luck. I believe it's probably specific data causing the problem, but since SQL Fiddle limits DDL statements to 8000 characters, which is a very small of the data I'm using, I might not be able to (I'd have to paste in 100 statements 80 times!) - Keith Davis
Ok, so there is something wrong with at least one record in the psych table as simple SELECT for p.id=4972 produces this same error. Why does it not cause this error with the SELECT in my question above? - Keith Davis

2 Answers

0
votes

Ok, this error had nothing to do with the calculated values.

When I run this, I get this same error (only for record with ID 4972):

SELECT p.id, p.pdqt_adm, p.pdqf_adm
FROM psych p
WHERE p.pdqt_adm>0 
    AND p.pdqt_adm IS NOT NULL 
    AND p.pdqf_adm>0 
    AND p.pdqf_adm IS NOT NULL 
    AND p.id=4972

As it turns out, the 2 columns being used to compare to 0 and to each other both contain the value "-". Now, why this does not affect my SELECT in my question above, but does affect my UPDATE...I have no idea.

0
votes

The update and select do not necessarily process the table in the same order. You can try:

UPDATE psych
SET pdqp_adm=CAST((CAST(pdqt_adm AS SIGNED)) - (CAST(pdqf_adm AS SIGNED)) AS CHAR)
    WHERE (case when pdqt_adm <> '-' then pdqt_adm else 0 end)  > 0 AND
          pdqt_adm IS NOT NULL AND
          (case when pdqf_adm <> '-' then pdqf_adm else 0 end) > 0 AND
          pdqf_adm IS NOT NULL AND
          (case when pdqt_adm <> '-' then pdqt_adm else 0 end) >= (case when pdqf_adm <> '-' then pdqf_adm else 0 end);