我试图构建一个触发器,在同一个表被更新之后,通过引用另一个表来更新表字段。我尝试了以下代码,但每次尝试都会出现错误#1064。有人能告诉我出了什么事吗。引用尝试1。
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
===结束尝试1
尝试2
BEGIN
UPDATE
a
SET
a.earned =
CASE WHEN a.incactualn < b.n1 THEN '0'
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
FROM
incent a
INNER JOIN
inc_prog b
ON
a.Incent_name = b.incname && a.period = b.finyear
END
=======结束尝试2
取消引用
1条答案
按热度按时间34gzjxbg1#
你只把case这个词放在case表达式的开头。只需删除第二份和随后的副本。所以你的第一个触发点是:
在第二次尝试中,缺少case表达式结尾处的结尾,以及子查询周围的(select and):