2

I am trying to solve the problem with insert new value into table. Primary key is ID not email... Before insert i need check other records for email value. If no one match inserted email, data is inserted. But i canot write correct code for that...

IF EXIST (SELECT email FROM users WHERE email = "email")  
THEN 
    BEGIN 
        'We Have Records of this Customer' 
    END                                          
ELSE 
    BEGIN 
        INSERT INTO users 
        VALUES (null,'email9','password9','forename9','lastname9')
    END
END IF

Or:

IF SELECT COUNT(*) FROM users WHERE email = 'email' > 0
THEN 
    BEGIN
    //email exist
    END
ELSE 
    BEGIN
    //inserting
    END
END IF
GMB
  • 216,147
  • 25
  • 84
  • 135
HxAxNxY
  • 43
  • 5

2 Answers2

1

This looks like a good use case for MySQL INSERT ... ON DUPLICATE KEY UPDATE syntax.

First of all, you want to create a UNIQUE constraint on column email. This is the proper way to represent your business rule:

ALTER TABLE users ADD CONSTRAINT users_unique_email UNIQUE (email);

Now, if the record that is about to be inserted would generate a duplicate on the primary key or on that UNIQUE constraint, then MySQL lets you turn the operation to an UPDATE instead.

In your case, since you want not to update anything in that case, you can simply re-assign the email column (which we know is the same here).

Consider:

INSERT INTO users 
    VALUES (null,'email9','password9','forename9','lastname9')
    ON DUPLICATE KEY UPDATE SET email = 'mail9';
GMB
  • 216,147
  • 25
  • 84
  • 135
0

I cant comment, but I post this. I recommend making an external script like in python or java to do this for you by selecting * and parsing through the output because SQL itself does not have the power to do this.

Ryan Wans
  • 28
  • 5