SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Truncated incorrect DOUBLE value in update query

    Hi guys, hope in your help.
    I tried this select query and I've this output:
    Code:
    mysql> SELECT
    	A,
    	B,
    	A * B AS output,
    	C
    FROM
    	tbl_2
    ORDER BY
    	output DESC;
    +-----+---+--------+------+
    | A   | B | output | C    |
    +-----+---+--------+------+
    | 252 | 7 |   1764 | NULL |
    | 134 | 7 |    938 | NULL |
    | 116 | 6 |    696 | NULL |
    | 560 | 1 |    560 | NULL |
    | 220 | 2 |    440 | NULL |
    | 161 | 2 |    322 | NULL |
    | 261 | 1 |    261 | NULL |
    | 192 | 1 |    192 | NULL |
    | 183 | 1 |    183 | NULL |
    | 164 | 1 |    164 | NULL |
    | 150 | 1 |    150 | NULL |
    | 126 | 1 |    126 | NULL |
    |  98 | 1 |     98 | NULL |
    |  79 | 1 |     79 | NULL |
    |   0 |   |      0 | NULL |
    |   0 |   |      0 | NULL |
    | 149 | 0 |      0 | NULL |
    |   0 |   |      0 | NULL |
    +-----+---+--------+------+
    18 rows in set
    Now I need update the field `C` of tbl_2 with the result of the product (A * B):
    Code:
    mysql> UPDATE tbl_2
    SET C = (
    	A * B
    );
    
    1292 - Truncated incorrect DOUBLE value: ''
    Why this error Truncated incorrect DOUBLE value ?
    The field C in the tbl_2 is varchar 255.
    Can you help me?
    Thank you

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    three comments

    first, you should rarely ever need to store the result of a row calculation -- it can always be calculated in a SELECT and you run the risk of data inconsistencies

    second, you shouldn't be using DOUBLE here for what are obviously integers -- DOUBLE is a floating point, i.e. approximate datatype

    third, when you ask for help on something like this, you should always do a SHOW CREATE TABLE so that we can see what the table definition looks like
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    three comments

    first, you should rarely ever need to store the result of a row calculation -- it can always be calculated in a SELECT and you run the risk of data inconsistencies

    second, you shouldn't be using DOUBLE here for what are obviously integers -- DOUBLE is a floating point, i.e. approximate datatype

    third, when you ask for help on something like this, you should always do a SHOW CREATE TABLE so that we can see what the table definition looks like
    thank you for comments, solved with second comment.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •