updating field with decimal values in database

Question

I have a database with 2 fields named "number" and "form" and need to alter some fields.

I have a list in excel with the values i.e 1234.5, 1233.7 where the 1234 is the number and the 5 is the form number in my table.

is it possible to do something like:

UPDATE `table` SET `field`='value' WHERE `number` IN (1234,1233) AND `form` IN (5,7)

the first problem I see is when I have two "numbers" that is the same but different "form" numbers.

or can I do something like:

UPDATE `table` SET `field`='value' WHERE CONCAT(number,'.',form) IN (1234.5,1233.7)

so is there any other way I can approach this?

Answer 1

UPDATE `table` SET `field`='value' WHERE CONCAT(number,'.',form) IN ('1234.5','1233.7')

does the job, just needed the IN ('1234.5','1233.7') instead of IN (1234.5,1233.7)

Answer 2

I am not exactly sure what you are trying to accomplish, but it sounds like the numbers would be different. Try finding the POSITION() of the "." in the number. Then, do a SUBSTRING() on those positions. It Would look like so:

UPDATE `table` 
SET `field`='value' 
WHERE number = SUBSTRING(input, 1, POSITION('.', input) - 1) 
      AND form = SUBSTRING(input, POSITION('.', input) + 1)

The following code will check the Number before the ".", and then the form after the ".".