mysql if not exist

Question

I am coming from MSSQL, and now using Mysql, this may be a easy one but how do you execute a if not exist statement. thanks

IF NOT EXISTS(SELECT * from users WHERE Username = spUsername)
    BEGIN
    INSERT into users(ID,Username,Password,Email,Birthdate,DateJoin)
            VALUES(UUID(),spUsername,spPassword,spEmail,spBirthDate,NOW());
    END

possible duplicate: http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — Matt Fenwick, Aug 18 '11 at 20:30

Loktar · Accepted Answer · 2011-08-18T20:32:27.510

4

Im not a database expert by any means but this is how I do it.

http://www.somethinghitme.com/2010/05/06/mysql-stored-procedures-if-not-exists/

    DECLARE SomeId int;
    DECLARE CheckExists int;
    SET CheckExists = 0;  

    SELECT 1 INTO CheckExists from lookup_table WHERE someField = in_SomeParam LIMIT 1;   

    IF (CheckExists > 0) THEN
        SELECT id INTO SomeId FROM lookup_table WHERE someField = in_SomeParam;
    ELSE
        INSERT INTO lookup_table (someField ) VALUES(in_SomeParam);
        SELECT SomeId = LAST_INSERT_ID();
    END IF;

edited Aug 18 '11 at 20:32

answered Aug 18 '11 at 20:25

Loktar

32,547
8
83
101

One thing to be aware of is the forced, unnecessary count of the filtered recordset in the select statement. Another approach would be `SELECT 1 INTO CheckExists FROM lookup_table WHERE someField = in_SomeParam LIMIT 1;` – Doug Kress Aug 18 '11 at 20:31

mysql if not exist

1 Answers1

Linked