mysql - SQL update a calculated column -

i have column in table need update. column computed this:

select case when timestampdiff(year, geburtstag, now()) <= 27 ((w_staerke/100*70) + (w_technik/100*30))             when timestampdiff(year, geburtstag, now()) <= 31 ((w_staerke/100*70) + (w_technik/100*30))        end marktwert _spieler; 

i want update column on records table.

can use like

update _spieler set marktwert = case when timestampdiff(year, geburtstag, now()) <= 27 ((w_staerke/100*70) + (w_technik/100*30))                 when timestampdiff(year, geburtstag, now()) <= 31 ((w_staerke/100*70) + (w_technik/100*30))            end; 

the query seems correct, sets value in "marktwert" 0 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 , sample data

update _spieler  set marktwert = case when timestampdiff(year, geburtstag, now()) <= 27 ((w_staerke/100*70) + (w_technik/100*30)) * 600000  when timestampdiff(year, geburtstag, now()) <= 31 (((w_staerke/100*70) + (w_technik/100*30)) * 600000) - 5000000  when timestampdiff(year, geburtstag, now()) > 31 (((w_staerke/100*70) + (w_technik/100*30)) * 600000) - 10000000 end 

final query added

your problem not calculate marktwert value players (spieler) older 31 years (geburtstag = birthday). update statement trying write null marktwert column, defined not null. , results in error.

solutions:

1) user else in case statement , set default value:

update _spieler set marktwert =     case          when timestampdiff(year, geburtstag, now()) <= 27 ((w_staerke/100*70) + (w_technik/100*30))         when timestampdiff(year, geburtstag, now()) <= 31 ((w_staerke/100*70) + (w_technik/100*30))         else 0     end; 

2) allow null value column marktwert:

create table `_spieler` (   ...   `marktwert` int(10) null default '0',   ... ) 

3) use where condition:

update _spieler set marktwert =     case          when timestampdiff(year, geburtstag, now()) <= 27 ((w_staerke/100*70) + (w_technik/100*30))         when timestampdiff(year, geburtstag, now()) <= 31 ((w_staerke/100*70) + (w_technik/100*30))     end timestampdiff(year, geburtstag, now()) <= 31; 

update: can remove marktwert column , use view (calculated table) instead:

create view `_spieler_view` select s.*,     case          when timestampdiff(year, geburtstag, now()) <= 27 ((w_staerke/100*70) + (w_technik/100*30))         when timestampdiff(year, geburtstag, now()) <= 31 ((w_staerke/100*70) + (w_technik/100*30))     end marktwert_calculated _spieler s ; 

update 2:

if use mariadb use virtual (computed) columns