MySQL stored procedure syntax IF else

Question

Guys i can not find solution for this problem , its always giving syntax error what ever i have tried ... can you just cast a look for me , thanks

create procedure SP_Insert(in MatchIDP int,in TipID int, in User int)
begin

if exists(
select BetSlipID from betslips where MatchID = MatchIDP and UserID = User)
(
   update Betslips set TipID = 2
)
else
(
   insert into Betslips (MatchID,TipID , UserID) value (MatchIDP,TipID,User)
)
end if
end

I just want to check if data exists in table before i do insert , and i can not use "On duplicate key update" because my primary key does not mean anything , its table where i put in 2-3 foreign keys ....

eggyal · Accepted Answer · 2012-09-08 20:08:09Z

2

Your IF syntax is incorrect. It should be:

delimiter ;;

create procedure SP_Insert(in MatchIDP int,in TipID int, in User int)
begin

if exists(
  select * from betslips where MatchID = MatchIDP and UserID = User
) then
  update Betslips set TipID = 2; -- where ?
else
  insert into Betslips (MatchID,TipID , UserID) values (MatchIDP, TipID, User);
end if;

end;;

However, if you will never permit duplicate (MatchID, UserID) entries in your Betslips, why not define a UNIQUE constraint across those columns and then use INSERT ... ON DUPLICATE KEY UPDATE:

ALTER TABLE Betslips ADD UNIQUE INDEX (MatchID, UserID);

INSERT INTO Betslips (MatchID, TipID, UserID) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE TipID = 2;

answered Sep 8, 2012 at 20:08

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

5 Comments

Veljko89 Over a year ago

thanks for answer mate , idea is that i never have one user to bet on same game more then once , that is reason why i am checking if that data exists

eggyal Over a year ago

@Veljko89: Then I'd go with my second suggestion, of defining an appropriate UNIQUE constraint and then using INSERT ... ON DUPLICATE KEY UPDATE.

Veljko89 Over a year ago

But INSERT ... ON DUPLICATE KEY UPDATE i can not check MatchID and UserID combo exists , and that is thing what matters to me... right?

eggyal Over a year ago

@Veljko89: That's exactly what it will do if you define the UNIQUE constraint as advised in my answer.

Veljko89 Over a year ago

hmm in that case ill google a bit more about it ... thanks a lot mate

mmdemirbas · Accepted Answer · 2012-09-08 20:10:49Z

0

CREATE PROCEDURE SP_Insert (IN MatchIDP INT, IN TipID INT, IN USER INT)
BEGIN
        DECLARE existing INT DEFAULT NULL;

        SELECT BetSlipID
        INTO   existing
        FROM   betslips
        WHERE  MatchID = MatchIDP
        AND    UserID = USER;

        IF existing is not null THEN
            UPDATE Betslips SET TipID = 2;
        ELSE
            INSERT INTO Betslips (MatchID, TipID, UserID)
            VALUES (MatchIDP, TipID, USER);
        END IF;

END

answered Sep 8, 2012 at 20:10

mmdemirbas

9,1966 gold badges49 silver badges55 bronze badges

2 Comments

mmdemirbas Over a year ago

@eggyal Then you can easily add an equal sign to existing > 0 => existing >= 0. I changed the condition, checking against null.

eggyal Over a year ago

Okay, what if there's a matching record with BetSlipID=NULL (perhaps not possible in the OP's data model, but nevertheless this is a bit sloppy... surely EXISTS was a more correct test?)

Collectives™ on Stack Overflow

MySQL stored procedure syntax IF else

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related