MYSQL:基于IF更新多列

Question

早上好，我告诉他们我的情况:我有两张桌子：

RATINGS:
GUIDE   varchar(7)
USER    varchar(20)
PRETEST decimal(4,3)
POSTEST decimal(4,3)

RATINGS_PERGUIDE:
USER    varchar(20)
GUIDE1_PRETEST  decimal(4,3)
GUIDE1_POSTEST  decimal(4,3)
GUIDE2_PRETEST  decimal(4,3)
GUIDE2_POSTEST  decimal(4,3)3
.
.
.
GUIDE20_PRETEST decimal(4,3)
GUIDE20_POSTEST decimal(4,3)

表“评级”有一个输入信息的表单，我需要将输入的信息移到"RATINGS_PERGUIDE“表中，我尝试如下：

UPDATE RATINGS , RATINGS_PERGUIDE
IF RATINGS.USER = RATINGS_PERGUIDE.USER AND RATINGS.GUIDE = 'GUIDE 1'
THEN 
SET RATINGS_PERGUIDE.GUIDE1_PRETEST = RATINGS.PRETEST
SET RATINGS_PERGUIDE.GUIDE1_POSTEST = RATINGS.POSTEST
ELSE IF RATINGS.USER = RATINGS_PERGUIDE.USER AND RATINGS.GUIDE = 'GUIDE 2'
THEN 
SET RATINGS_PERGUIDE.GUIDE2_PRETEST = RATINGS.PRETEST;
SET RATINGS_PERGUIDE.GUIDE2_POSTEST = RATINGS.POSTEST;
.
.
.
END IF

但不管用

ie，根据“指南”在表中输入的“评级”更新表中相应的列"RATINGS_PERGUIDE“(24”指南“不同)

同样，如果可以在插入到表中的每个新记录的触发器中完成，“评级”将更好。

请帮帮忙

Answer 1

我通过观察Phpmyadmin中的“更新”结构找到了解决方案。

UPDATE RATINGS , RATINGS_PERGUIDE
SET RATINGS_PERGUIDE.GUIDE1_PRETEST = (CASE WHEN RATINGS.GUIDE = 'GUIDE 1' THEN RATINGS.PRETEST ELSE RATINGS_PERGUIDE.GUIDE1_PRETEST END),
RATINGS_PERGUIDE.GUIDE1_POSTEST = (CASE WHEN RATINGS.GUIDE = 'GUIDE 1' THEN RATINGS.PRETEST ELSE RATINGS_PERGUIDE.GUIDE1_POSTEST END),
RATINGS_PERGUIDE.GUIDE2_PRETEST = (CASE WHEN RATINGS.GUIDE = 'GUIDE 2' THEN RATINGS.PRETEST ELSE RATINGS_PERGUIDE.GUIDE2_PRETEST END),
RATINGS_PERGUIDE.GUIDE2_POSTEST = (CASE WHEN RATINGS.GUIDE = 'GUIDE 2' THEN RATINGS.PRETEST ELSE RATINGS_PERGUIDE.GUIDE2_POSTEST END),
. . . .
WHERE RATINGS.USER = RATINGS_PERGUIDE.USER

对于我来说，我把它放在一个存储过程中，然后用一个触发器调用

感谢所有的人和Gordon Linoff

Answer 2

您正试图在update中完成一个支点，这有点复杂。一种方法是简单地执行多个更新：

update RATINGS_PERGUIDE join
       RATINGS
       on RATINGS.USER = RATINGS_PERGUIDE.USER and
          RATINGS.GUIDE = 'GUIDE 1'
    SET RATINGS_PERGUIDE.GUIDE1_PRETEST = RATINGS.PRETEST,
        RATINGS_PERGUIDE.GUIDE1_POSTEST = RATINGS.POSTEST;

为每个指南修改并重复此更新。

如果您真的想在一个查询中这样做：

update RATINGS_PERGUIDE join
       (select r.user,
               max(case when r.GUIDE = 'GUIDE 1' then pretest end) as pretest_guide1,
               max(case when r.GUIDE = 'GUIDE 1' then posttest end) as posttest_guide1,
               max(case when r.GUIDE = 'GUIDE 2' then pretest end) as pretest_guide2,
               max(case when r.GUIDE = 'GUIDE 2' then posttest end) as posttest_guide2,
               . . .
        from RATINGS r
        group by r.user
       ) r
       on r.USER = RATINGS_PERGUIDE.USER
    SET RATINGS_PERGUIDE.GUIDE1_PRETEST = r.pretest_guide1,
        RATINGS_PERGUIDE.GUIDE1_POSTEST = r.posttest_guide1,
        RATINGS_PERGUIDE.GUIDE2_PRETEST = r.pretest_guide2,
        RATINGS_PERGUIDE.GUIDE2_POSTEST = r.posttest_guide2,
        . . .