UPDATE if exists else INSERT in SQL

Question

I'm trying to implement a SQL query to "UPDATE if exists else INSERT"

My table(Allowance) is as below:

EmployeeID int(8) PK
Year year(4) PK
Month int(2) PK
OverTime decimal(10,2)
Medical decimal(10,2)
Lunch decimal(10,2)
Bonus decimal(10,2)
Allowance decimal(10,2)

Below is the SQL query I tried:

IF EXISTS (SELECT * FROM Allowance WHERE EmployeeID =10000001 and Year = 2014 and Month = 4)
    UPDATE Allowance
    SET OverTime = 10.00, Medical = 10.00, Lunch = 10.45, Bonus =10.10, Allowance = 40.55
    WHERE EmployeeID =10000001 and Year = 2014 and Month = 4
ELSE
    INSERT into Allowance values (10000001,2014,4,10.00,10.00,10.45,10.10,40.55)

I keep getting this error message:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS (SELECT * FROM Allowance WHERE EmployeeID =10000001 and Year = 2014 an' at line 1 "

Can somebody please help??

Answer 1

The below query will fulfill your requirement.

INSERT INTO `ALLOWANCE` (`EmployeeID`, `Year`, `Month`, `OverTime`,`Medical`,
`Lunch`, `Bonus`, `Allowance`) values (10000001, 2014, 4, 10.00, 10.00,
10.45, 10.10, 40.55) ON DUPLICATE KEY UPDATE `EmployeeID` = 10000001

Answer 2

With this procedure you can check if exist or not and then update/insert as you want

 DELIMITER $$;   
 CREATE PROCEDURE example()

BEGIN
DECLARE vexist int;

  SELECT count(*) into vexist FROM Allowance --count because i will
  WHERE EmployeeID =10000001 and Year = 2014 and Month = 4;  --this will check if exist or not

    IF (vexist >= 1) then  --if exist then update
    UPDATE Allowance
        SET OverTime = 10.00, Medical = 10.00, Lunch = 10.45, Bonus =10.10, Allowance = 40.55
        WHERE EmployeeID =10000001 and Year = 2014 and Month = 4;
    ELSE
      INSERT into Allowance values (10000001,2014,4,10.00,10.00,10.45,10.10,40.55);
END IF;
END $$
DELIMITER ;

You have to call the procedure now:

call example();

Note: This will solve you solution as for now, but it's not the best idea since procedures are intended to get used in the future aswell, so i'll give you an improved version where in the future you will be able to update/insert by just invoking the procedure and wrinting your inserts values.

DELIMITER $$;   
     CREATE PROCEDURE example(
IN
vempid int(8),
vyear year(4), 
vmonth int(2),
vovertime float(10,2),
vmedical float(10,2),
vlunch float(10,2),
vbonus float(10,2),
vallowance float(10,2))

    BEGIN
    DECLARE vexist int;

      SELECT count(*) into vexist FROM Allowance --count because i will
      WHERE EmployeeID =vemp and Year = vyear and Month = vmonth;  --this will check if exist or not

        IF (vexist >= 1) then  --if exist then update
        UPDATE Allowance
            SET OverTime = vovertime, Medical = vmedical, Lunch = vlunch, Bonus = vbonus, Allowance = vallowabce
            WHERE EmployeeID =10000001 and Year = vyear and Month = vmonth;
        ELSE
          INSERT INTO `ALLOWANCE` (`EmployeeID`, `Year`, `Month`, `OverTime`,`Medical`,`Lunch`, `Bonus`, `Allowance`) values (vempid,vyear,vmonth,vovertime,vmedical,vlunch,vbonus,vallowance);
    END IF;
    END $$
    DELIMITER ;

And invoking (with the correct parameters order):

call example2(10000001,2014,4,10.00,10.00,10.45,10.10,40.5);