bloopiebloopie bloopiebloopie - 7 months ago 7
SQL Question

MySQL stored procedure; multi statment procedure, throws error

UPDATE Decks
JOIN Amount ON amount.DeckName = decks.DeckName
SET decks.DeckTotal = Decks.DeckTotal - Decks.DeckTotal
WHERE Amount.AmountName = @aName;

UPDATE Types t1
JOIN Cards ON cards.TypeName = t1.TypeName
JOIN Amount ON amount.CardName = Cards.CardName
SET t1.TypeTotal = t1.TypeTotal - Amount.Amount
WHERE Amount.CardName = @aName;

DELETE *
FROM Amount
WHERE CardName = @aName;


The above is throwing an error when I try and put it into a stored procedure using phpMyAdmin. It says there is an error in my syntax as soon as the second statement begins.

Attached is a screenshot of the erroer

Answer

remove * from your delete query..Then it will work fine

CREATE  PROCEDURE `sp_test`()
Begin
      UPDATE Decks 
      JOIN Amount ON amount.DeckName = decks.DeckName 
      SET decks.DeckTotal = Decks.DeckTotal - Decks.DeckTotal
      WHERE Amount.AmountName =1;

      UPDATE Types t1
      JOIN Cards ON cards.TypeName = t1.TypeName 
      JOIN Amount ON amount.CardName = Cards.CardName
      SET t1.TypeTotal = t1.TypeTotal - Amount.Amount
      WHERE Amount.CardName = @aName;

      DELETE 
      FROM Amount 
      WHERE CardName = @aName;
 end
Comments