Malgunda Malgunda - 7 months ago 24
SQL Question

SQL update a calculated column

I have a column in a table which I need to update. The column is computed like this:

SELECT CASE WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 27 THEN ((w_staerke/100*70) + (w_technik/100*30))
WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 31 THEN ((w_staerke/100*70) + (w_technik/100*30))
END AS marktwert
FROM _spieler;


I want to update column on all the records from that table.

Can I use something like

UPDATE _spieler SET marktwert = CASE WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 27 THEN ((w_staerke/100*70) + (w_technik/100*30))
WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 31 THEN ((w_staerke/100*70) + (w_technik/100*30))
END;


The query seems to be correct, however it sets the value in "marktwert" to zero for every row.

CREATE TABLE `_spieler` (
`id` int(10) NOT NULL,
`vorname` varchar(30) DEFAULT NULL,
`nachname` varchar(30) DEFAULT NULL,
`geburtstag` date NOT NULL,
`w_staerke` tinyint(3) NOT NULL,
`w_technik` tinyint(3) NOT NULL,
`marktwert` int(10) NOT NULL DEFAULT '0',
`age` tinyint(3) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=535 ;

(1, 'Adam', 'Federici', '1985-01-31', 30, 20, 0, NULL),
(2, 'Ryan', 'Allsop', '1992-06-17', 20, 30, 0, NULL),
(3, 'Tyrone', 'Mings', '1980-03-13', 40, 20, 0, NULL),
(4, 'Joe', 'Bennett', '1990-03-28', 25, 30, 0, NULL),
(5, 'Charlie', 'Daniels', '1986-09-07', 50, 30, 0, NULL);


table definition and some sample data

UPDATE _spieler
SET marktwert = CASE WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 27 THEN ((w_staerke/100*70) + (w_technik/100*30)) * 600000
WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 31 THEN (((w_staerke/100*70) + (w_technik/100*30)) * 600000) - 5000000
WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) > 31 THEN (((w_staerke/100*70) + (w_technik/100*30)) * 600000) - 10000000 END


final query added

Answer

Your problem is that you do not calculate any marktwert value for players (Spieler) who is older than 31 years (geburtstag = birthday). Your UPDATE statement is trying to write NULL into the marktwert column, which is defined as NOT NULL. And that results in an error.

Solutions:

1) User ELSE in your CASE statement and set a default value:

UPDATE _spieler SET marktwert =     CASE 
        WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 27 THEN ((w_staerke/100*70) + (w_technik/100*30))
        WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 31 THEN ((w_staerke/100*70) + (w_technik/100*30))
        ELSE 0
    END;

2) Allow NULL value for column marktwert:

CREATE TABLE `_spieler` (
  ...
  `marktwert` int(10) NULL DEFAULT '0',
  ...
)

3) Use a WHERE condition:

UPDATE _spieler SET marktwert =     CASE 
        WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 27 THEN ((w_staerke/100*70) + (w_technik/100*30))
        WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 31 THEN ((w_staerke/100*70) + (w_technik/100*30))
    END
WHERE TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 31;

Update: You can also remove the marktwert column and use a view (calculated table) instead:

CREATE VIEW `_spieler_view` AS SELECT s.*,
    CASE 
        WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 27 THEN ((w_staerke/100*70) + (w_technik/100*30))
        WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <= 31 THEN ((w_staerke/100*70) + (w_technik/100*30))
    END AS marktwert_calculated
from _spieler s ;

Update 2:

If you use MariaDB you could also use Virtual (Computed) Columns