MySql if else statement, if not valid in this position

Question

I have a vet table and a medical table with a 1 to many relationship, and the ID's are auto incremented.

CREATE TABLE vet(
      vetID INT NOT NULL AUTO_INCREMENT,
      vetPractice varchar(35),
      Address varchar(150),
      contactNumber varchar (15),
      PRIMARY KEY (VetID)
      );


CREATE TABLE medical(
      medicalID INT NOT NULL AUTO_INCREMENT,
      medication VARCHAR (200),
      PRIMARY KEY (medicalID),
      FOREIGN KEY (vetID) REFERENCES vet(vetID)
      );

Users can enter details of a vet, i want a query to determine;

if the the vet details entered already exist, then update the foreign key in vetID(medical) with the entered vetID.
else if the vet does not exist create a new vet and update the foreign key in vetID(medical) with the newly created vetID.

I have the following query

IF EXISTS(SELECT * FROM vet WHERE vetPractice = "inputValue") 
    THEN
    UPDATE medical set value vetID = (Select max(vetID) from vet)
    ELSE 
    INSERT INTO vet values (null, "newVetPractice", "NewAddress", "newContactNumber", "NewEmergencyNumber" );
    Then
    update  medical set value vetID = (Select max(vetID) from vet);
END IF;

However, i am not familiar with if else's in mySQL is this the correct format, i have seen somethings about stored procedures.

Any help would be appreciate.

Is this query in a stored procedure - IF - ELSE is only valid there. Also your syntax is incorrect - the last "Then" is not required. This may be relevant : http://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists — PaulF, Jul 15 '15 at 13:14

score 1 · Answer 1 · answered Jul 15 '15 at 13:27

I'm not really clear about your logic; but it seems like you wanted it in a stored procedure format.

CREATE PROCEDURE 'sp_Med' (IN 'in_vetPractice' VARCHAR(35))  
LANGUAGE SQL  
NOT DETERMINISTIC  
CONTAINS SQL  
SQL SECURITY DEFINER  
BEGIN
    DECLARE ckExists int;  
    SET ckExists = 0;  

    SELECT count(*) INTO ckExists from vet WHERE vetPractice = in_vetPractice;   

    IF (ckExists > 0) THEN 
        UPDATE medical SET vetID = (Select max(vetID) FROM vet WHERE vetPractice = in_vetPractice) 
    ELSE 
        INSERT INTO vet VALUES (NULL, "newVetPractice", "NewAddress", "newContactNumber", "NewEmergencyNumber");
        UPDATE medical SET vetID = LAST_INSERT_ID();    
    END IF; 
END;

Execute it like

CALL sp_Med('newPractice')

score 0 · Answer 2 · answered Jul 15 '15 at 13:21

I think you have to update your query, and this is the general syntax you have to use rather tha n yours:-

INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`);

This query will insert records if they are not present, and on presence it will update them.

So use this rather than your approach

MySql if else statement, if not valid in this position

2 Answers2