SQL 更新计算列 [英] 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;

该查询似乎是正确的，但是它将每一行的marktwert"中的值设置为零.

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);

表定义和一些示例数据

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

添加了最终查询

推荐答案

您的问题是您没有为 31 岁以上的玩家 (Spieler) 计算任何 marktwert 值 (geburtstag =birthday)).您的 UPDATE 语句试图将 NULL 写入 marktwert 列，该列定义为 NOT NULL.这会导致错误.

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.

解决方案:

1) 在您的 CASE 语句中使用 ELSE 并设置默认值:

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) 允许列 marktwert 的 NULL 值:

2) Allow NULL value for column marktwert:

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

3) 使用 WHERE 条件:

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;

更新:您还可以删除 marktwert 列并使用 查看(计算表):

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 ;

更新 2:

如果您使用 MariaDB，您还可以使用虚拟(计算)列

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

这篇关于SQL 更新计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！