CODEWITHSUNDEEP
mysqlsqlinsert
S.Chern
S.Chern

Reputation: 27

Mysql check if not exist before multiple insert

I'm using mysql and I want to insert multiple statement at once. However, I need to check the email has been used before insert. If the email has been used, none of the statement will be execute. If the email have not been used, all the statement will be executed at once.

Currently, I'm using the statement below, is there any better way to achieve same result?

INSERT INTO user (user_id, user_name, password) 
SELECT ?, ?, ? FROM user
WHERE NOT EXISTS( 
SELECT email FROM user_profile WHERE email = ? 
) LIMIT 1; 

INSERT INTO customer_acc (customer_id, user_id) 
SELECT ?, ? FROM user
WHERE NOT EXISTS( 
SELECT email FROM user_profile WHERE email = ? 
) LIMIT 1; 

INSERT INTO user_profile (user_id, name, email) 
SELECT ?, ?, ? FROM user
WHERE NOT EXISTS( 
SELECT email FROM user_profile WHERE email = ? 
) LIMIT 1;

Upvotes: 0

Views: 103

Answers (2)

Akina
Akina

Reputation: 42764

Test:

SELECT COUNT(email) INTO @flag FROM user_profile WHERE email = ?;

INSERT INTO user (user_id, user_name, password) 
SELECT ?, ?, ? WHERE NOT @flag;

INSERT INTO customer_acc (customer_id, user_id) 
SELECT ?, ? WHERE NOT @flag;

INSERT INTO user_profile (user_id, name, email) 
SELECT ?, ?, ? WHERE NOT @flag;

All 4 queries must be executed in the same connection (and without intermediate re-connection).

Upvotes: 1

BlackBahamut
BlackBahamut

Reputation: 21

May I suggest you use procedures like this :

CREATE PROCEDURE ADD_USER()
BEGIN
    IF EXISTS (
        SELECT *
        FROM user_profile WHERE email = ___ 
        )
    THEN
        HERE YOUR REQUEST
    END IF;
END;

CALL ADD_USER();

DROP PROCEDURE IF EXISTS ADD_USER;

Upvotes: 0

Related Questions