SQL syntax error on update in trigger using variable

Question

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

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

@Arvo I'm using MySQL, I don't think I have to declare the variable in mySQL, thanks! — bloopiebloopie
– bloopiebloopie, Commented Apr 28, 2016 at 14:07

Stivan · Accepted Answer · 2016-05-09 11:23:22Z

1

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

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.

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;

edited May 9, 2016 at 11:23

answered Apr 28, 2016 at 13:57

Stivan

1,1271 gold badge15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

bloopiebloopie Over a year ago

Hey, what do you mean after set I need to add the table old? Thanks! Also the Soloution2 is what I had started with and yes the error is in the where clause.

Stivan Over a year ago

Look at "solution2". When you reference in the where clause "deckname = old.deckname" you need to use "FROM" old because you are referencing that table in you where clause. Or what is the exact error you receive?

bloopiebloopie Over a year ago

Oh sorry didn't see that, I'll try it now.

Stivan Over a year ago

Another way to test is to hardcode deckname in the where clause: WHERE deckname = 'somename';

bloopiebloopie Over a year ago

Hey tried testing with some name and still got an error. I have added the error to the code above! I hope this helps.

|

Collectives™ on Stack Overflow

SQL syntax error on update in trigger using variable

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related