Mysql Question posted in
The official MySQL documentation can be found here.

Change Data Type for MYSQL UPDATE for error 1292 Truncated Error Double Value on WHERE failing on VARCHAR(45)

I have looked at all error 1292 Truncated Error Double Value questions, but have not been able to derive a solution from them.

Answers have however led me to an assumtion that I am failing to prove:

I am querying MYSQL out of NODEJS using the MYSQL2 library. My table named shoptableplayground contains a column/field named regular_price. It has the data type attribute VARCHAR(45).

I would like to increase the value of said column/field by 10% for all rows.

I am getting error 1292 from the following query:

UPDATE shoptableplayground 
SET regular_price = regular_price * 1.1 WHERE regular_price BETWEEN 1 AND 200;

I am assuming that I am getting the error because WHERE expects to compare numerical data types (DEC, INT etc.) in order to be able to do the actual comparison.

My various attempts of incoorporating CONVERT or CAST or even ALTER TABLE MODIFY COLUMN datatype all lead to MYSQL SYNTAX ERRORS. Unfortunately most tutorials cover either SELECT or some form of direct data manipulation.

I’ve also tried rebuilding the table with a DECIMAL(10.2) column for column regular_price, but the populating process (outside my control) seems to insert the Data as string / VARCHAR nevertheless.

Any query suggestion on how to solve the issue would be much appreciated.

Tags:

2

Answers


  1. 0

    First of all, It’s a big mistake to store a calculated number as a VARCHAR type, but we’ll ignore it for now.

    Focus, you have a column contains many rows filled by prices(floats) but it is considered as a VARCHAR column we cannot do calculations on it.

    The Trick is Casting two times :

    • the 1st time : Casting from VARCHAR to FLOAT to do the desired calculation
    • the 2nd time : casting the result of the calculation which considered as a FLOAT
      Type to The Column Type(VARCHAR)

    also you will cast the condition the ensure the interval of the price(between 0 and 200)

    UPDATE shoptableplayground
SET regular_price = CAST(CAST(regular_price AS DECIMAL(10,2)) * 1.1 AS CHAR)
WHERE CAST(regular_price AS DECIMAL(10,2)) BETWEEN 1 AND 200;

    Edit :
    Since you use the comma (,) as a decimal seperator and the MySQL expects periods (.) as decimal seperators, you should replace comma by periods.

    UPDATE shoptableplayground
SET regular_price = CAST(CAST(REPLACE(regular_price, ',', '.') AS DECIMAL(10, 2)) * 1.1 AS CHAR)
WHERE CAST(REPLACE(regular_price, ',', '.') AS DECIMAL(10, 2)) BETWEEN 1 AND 200;
    Login or Signup to reply.
  2. 0

    you can’t keep working with this database and you don’t follow the rules.

    So first you need to change the column type to be float and following the US/English Notation of numbering and changing the old rows to to comply with the new type.

    and to stay out of risk, we will create a new column to hold the successfully converted rows, then we will remove the original row and renaming the new column as if nothing has changed.

    apply these queries separately, if one failed don’t apply the next until you resolve the first query problem

    /* create new column with the suitable type */
ALTER TABLE shoptableplayground
ADD COLUMN new_regular_price DECIMAL(10, 2);

    /* filling the new column with the well-formated data */
UPDATE shoptableplayground
SET new_regular_price = CAST(REPLACE(regular_price, ',', '.') AS DECIMAL(10, 2));

    /* remove the old column and rename the new column with the old column name*/
ALTER TABLE shoptableplayground
DROP COLUMN regular_price;
ALTER TABLE shoptableplayground
RENAME COLUMN new_regular_price TO regular_price;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search