I have an error in my SQL syntax when I convert the query from default floating-point to decimal poi-CodePudding

Using PHP and MariaDB my knowledge is fairly good. My knowledge in running 100% MariaDB is stack overflow and Google and online courses. I googled how to fix this issue converting to decimal point for 2 hours. Found this out use 1 of these 2 “AS DECIMAL(8,5)” or “DECIMAL(8,5)” to convert to decimal none of these work for me.

All the percentage columns in the table are all decimal(8,5). I read on Google when you run a SQL query defaults too floating-point. My problem is I do not know how to convert to decimal(8,5), I need 100% accuracy.

I have the table down below and the SQL query down below. Can you please show me how to convert it to decimal.

I have shortened table names to make it look better.

Table

1_player	2_player	3_player	id1_TA	id2_TA	id3_TA	avg_TA
2	16	30	85.00000	100.00000	100.00000	100.00000
10	9	9	100.00000	100.00000	100.00000	100.00000
11	2	15	100.00000	85.00000	100.00000	100.00000

Code

UPDATE test 
SET `avg_TA_percent` = (`id1_TA_percent`   `id2_TA_percent`  `id3_TA_percent`) / 3
WHERE `id1_TA_player` = 2 || `id2_TA_player` = 2 || `id3_TA_player` = 2;

Thank you for your help.

CodePudding user response：

UPDATE test
SET avg_TA = (id1_TA   id2_TA   id3_TA) / 3
WHERE 2 IN (1_player, 2_player, 3_player);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=47baf4517bf96643d6c2e16f5243fc42

Or maybe you mean that now your column are FLOAT and you want to change the datatype and update the average values?

ALTER TABLE test
    MODIFY COLUMN id1_TA DECIMAL(8,5),
    MODIFY COLUMN id2_TA DECIMAL(8,5),
    MODIFY COLUMN id3_TA DECIMAL(8,5),
    MODIFY COLUMN avg_TA DECIMAL(8,5);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f001d75383c039a293055d58c2499550

Think also about changing static averages column to generated one. In this case it will always provide actual value without recalculation:

ALTER TABLE test
    MODIFY COLUMN id1_TA DECIMAL(8,5),
    MODIFY COLUMN id2_TA DECIMAL(8,5),
    MODIFY COLUMN id3_TA DECIMAL(8,5),
    DROP COLUMN avg_TA,
    ADD COLUMN avg_TA DECIMAL(8,5) AS ((id1_TA   id2_TA   id3_TA) / 3);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bf086aafc164ec82aaef717ee6d07707

PS. It is impossible to achieve 100% accuracy. None datatype can store, for example, 88.(3) which is 88⅓ - see the last fiddle.

PPS. Column names are taken from the sample data.