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

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.

Solution

Unfortunatelely we were only able to solve the issue by providing the data the correct (english formatted, decimals with . not ,) way.

Neither Node JS nor MYSQL Terminals had any problems with the data type of the column being VARCHAR(45) hereafter.

All attempts of trying to replace , vs . within a MYSQL query, or changing the data type of the field failed for reasons beyond my noobish scope.

TYVM for everyone who helped! For time constraiting reasons I have to move on, but will review in some time. TY!