CODEWITHSUNDEEP
mysql
SeriesBlue
SeriesBlue

Reputation: 21

Inserting into a table only if record does not exists

I am trying to insert a user into users table only if their email does not exists.

CREATE TABLE `users` ( 
`ID` INT(8) NOT NULL AUTO_INCREMENT , 
`FIRSTNAME` VARCHAR(150) NOT NULL , 
`LASTNAME` VARCHAR(150) NOT NULL , 
`EMAIL` VARCHAR(150) NOT NULL , 
`PASSWORD` VARCHAR(150) NOT NULL , 
PRIMARY KEY (`ID`(8)), 
UNIQUE (`EMAIL`)
) ENGINE = InnoDB CHARSET=utf8 COLLATE utf8_bin;

And i am running this query:

SET @P1 = 'john';
SET @P2 = 'smith';
SET @P3 = '[email protected]';
SET @P4 = 'password';

I have USERS_INSERT_NEW is a stored procedure it simply does an insert into users table 

SELECT IF ( NOT EXISTS ( SELECT `ID` FROM `users` WHERE `EMAIL` = '[email protected]' ), CALL USERS_INSERT_NEW ( @P1, @P2, @P3, @P4 ) , NULL ) INSERTED

IF Statement works like this: IF ( expression, valid, invalid ) why can't i use my stored procedure if it validates ?

Upvotes: 0

Views: 86

Answers (2)

SeriesBlue
SeriesBlue

Reputation: 21

After further reading about stored procedures I found out that If statements only works inside of the stored procedures in MySQL. I am coming with SQL background which is so much easier to use. Anyways i solved this issue by selecting from users table the ID of users that's associated with this email if found, I would warn the user that email exists, else i inserted the record into the database.

Upvotes: 0

chows2603
chows2603

Reputation: 176

An easier way to do this is

INSERT INTO table_a
SELECT  b.*
FROM    table_b b
        LEFT JOIN table_a a
            ON a.ID = b.ID
WHERE   a.ID IS NULL

http://sqlfiddle.com/#!2/792d4/2

Upvotes: 1

Related Questions