im using
CREATE FUNCTION UPDATEGames(usernameIN CHAR(15) ,opponientIN CHAR(15) ,mFilePath TINYTEXT,oFilePath TINYTEXT)
BEGIN
UPDATE games
IF (username = opponientIN AND FilePath = oFilePath) THEN SET opLastTurn = NOW(),Turn=Turn+1
ELSEIF (username = usernameIN AND FilePath = mFilePath) THEN SET myLastTurn = NOW(),Turn=Turn+1
END IF
END;
but it didnt worked, what am i ding wrong?and if it isnt possible to do it so then how can i simulate this logic?
Within the update you can only use the if function, not the if statement. The if statement can only be used to execute different sql statements. Furthermore, the where criteria must be used in the update to restrict the records to be updated.
So, there is no need to use if statement at all, just use two updates. There is no return value either, so I also changed function to procedure.
DELIMITER //
CREATE PROCEDURE UPDATEGames(usernameIN CHAR(15) ,opponientIN CHAR(15) ,mFilePath TINYTEXT,oFilePath TINYTEXT)
BEGIN
UPDATE games SET opLastTurn = NOW(),Turn=Turn+1 WHERE username = opponientIN AND FilePath = oFilePath;
UPDATE games SET myLastTurn = NOW(),Turn=Turn+1 WHERE username = usernameIN AND FilePath = mFilePath;
END//
DELIMITER ;
UPDATE games set opLastTurn = NOW(),Turn=Turn+1 where username = opponientIN AND FilePath = oFilePath(and a 2nd update forusernameIN), without the if-thingy. Have an index on username, filepath. Also, a function needs to return something, so maybe use a procedure.