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_

Solution 1:

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 =CASEWHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <=27THEN ((w_staerke/100*70) + (w_technik/100*30))
        WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <=31THEN ((w_staerke/100*70) + (w_technik/100*30))
        ELSE0END;

2) Allow NULL value for column marktwert:

CREATETABLE `_spieler` (
  ...
  `marktwert` int(10) NULLDEFAULT'0',
  ...
)

3) Use a WHERE condition:

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

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

CREATEVIEW `_spieler_view` ASSELECT s.*,
    CASEWHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <=27THEN ((w_staerke/100*70) + (w_technik/100*30))
        WHEN TIMESTAMPDIFF(YEAR, geburtstag, NOW()) <=31THEN ((w_staerke/100*70) + (w_technik/100*30))
    ENDAS marktwert_calculated
from _spieler s ;

Update 2:

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