使用变量触发器更新时的SQL语法错误

I am trying to update a table as a trigger is called and I am getting a syntax error on the update statement.

我试图更新一个表作为触发器被调用,我在更新语句上收到语法错误。

BODY OF TRIGGER:

触发者身体:

set @diff = old.amount -new.amount;
UPDATE decks
SET decktotal = decktotal + @diff
WHERE deckname = old.deckname;

I will add more information if needed but I imagine this to be a particularity stupid oversight on my part that is easily rectified. Thanks for lookin either way!

如果需要,我会添加更多信息,但我认为这是一个特殊的愚蠢的疏忽,我很容易纠正。谢谢你们两种方式!

EDIT: ERROR 1064 error in syntax at

编辑:语法错误1064错误

UPDATE decks
SET decktotal = decktotal + @diff
FROM old
WHERE deckname = old.deckname;

1 个解决方案

#1

I assume the problem you are having is in the where clause you use:

我假设您遇到的问题在于您使用的where子句:

WHERE deckname = old.deckname;

the problem with this is table old is not specified in the update. To solve this create a variable and assign it to the deckname in the where clause. Or after set you need to add the table old.

这个问题是更新中未指定table old。要解决此问题,请创建一个变量并将其分配给where子句中的deckname。或者在设置之后,您需要添加旧表。

Solution1:

declare @old_name varchar(20);     //Disregard if you don't need to declare
select @old_name = old.deckname;
SET @diff = old.amount -new.amount;
UPDATE decks
SET decktotal = decktotal + @diff
WHERE deckname = @old_name;

Solution2:

SET @diff = old.amount -new.amount;
UPDATE decks
SET decktotal = decktotal + @diff
FROM old
WHERE deckname = old.deckname;

#1