CODEWITHSUNDEEP
mysqlsql
brad
brad

Reputation: 878

MySQL INSERT INTO with dual condition for IF NOT EXIST

I am trying to insert a new record if the email address does not exist in list_email.email_addr AND not exist in list_no_email.email_addr

INSERT INTO list_email(fname, lname, email_addr) VALUES('bob', 'schmoe', '[email protected]'), ('mary', 'lamb', '[email protected]');
SELECT email_addr FROM list_email
WHERE NOT EXIST(
SELECT email_addr FROM email_addr WHERE email_addr = $post_addr
) 
WHERE NOT IN (
SELECT email_addr FROM list_no_email WHERE email_addr = $post_addr
)LIMIT 1


**************************** mysql tables ******************************

mysql> desc list_email;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| list_name  | varchar(55)  | YES  |     | NULL    |                |
| fname      | char(50)     | YES  |     | NULL    |                |
| lname      | char(50)     | YES  |     | NULL    |                |
| email_addr | varchar(150) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

mysql> desc list_no_email;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| date_in    | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| email_addr | varchar(150) | YES  |     | NULL              |                             |
+------------+--------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

***************** error **************

INSERT INTO list_email(fname, lname, email_addr) VALUES('bob', 'schmoe', '[email protected]'), ('mary', 'lamb', '[email protected]'); SELECT email_addr FROM list_email WHERE NOT EXIST( SELECT email_addr FROM email_addr WHERE email_addr = $post_addr )  WHERE NOT IN ( SELECT email_addr FROM list_no_email WHERE email_addr = $post_addr )LIMIT 1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT email_addr FROM email_addr WHERE email_addr = $post_addr )  WHERE NOT IN ' at line 1

************* without ; ****************

mysql> INSERT INTO list_email(fname, lname, email_addr) VALUES('bob', 'schmoe', '[email protected]'), ('mary', 'lamb', '[email protected]')
    -> SELECT email_addr FROM list_email AS tmp
    -> WHERE NOT EXIST(
    -> SELECT email_addr FROM email_addr WHERE email_addr = $post_addr
    -> )
    -> WHERE NOT IN (
    -> SELECT email_addr FROM list_no_email WHERE email_addr = $post_addr
    -> )LIMIT 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT email_addr FROM list_email AS tmp
WHERE NOT EXIST(
SELECT email_addr FROM' at line 2

Upvotes: 1

Views: 3884

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

INSERT INTO list_email
  (fname, lname, email_addr)
SELECT ins.*
FROM
  ( SELECT 'bob' AS fname, 
           'schmoe' AS lname, 
           '[email protected]' AS email_addr
    FROM dual
  UNION ALL                               -- for every row to be inserted
    SELECT 'mary', 'lamb', '[email protected]'
    FROM dual 
                                          -- add another UNION ALL .. FROM dual
  ) AS ins
WHERE NOT EXISTS
      ( SELECT 1 
        FROM email_addr AS e 
        WHERE e.email_addr = ins.email_addr
      )
  AND NOT EXISTS
      ( SELECT 1 
        FROM list_no_email AS ne 
        WHERE ne.email_addr = ins.email_addr
      ) ;

Upvotes: 0

Dipesh Parmar
Dipesh Parmar

Reputation: 27382

Replace

WHERE NOT EXIST(

With

WHERE email_addr NOT IN(

EDIT

Replace

SELECT email_addr FROM list_email
WHERE NOT IN
(
    SELECT email_addr FROM email_addr WHERE email_addr = $post_addr
) 
WHERE NOT IN
(
    SELECT email_addr FROM list_no_email WHERE email_addr = $post_addr
)LIMIT 1

with

SELECT email_addr FROM list_email
WHERE NOT IN
(
    SELECT email_addr FROM email_addr WHERE email_addr = $post_addr
    UNION ALL
    SELECT email_addr FROM list_no_email WHERE email_addr = $post_addr
)
LIMIT 1

Upvotes: 0

7alhashmi
7alhashmi

Reputation: 924

Try something like this:

 INSERT INTO list_email($username.$rowname, fname, lname, list_email) VALUES(?,?,?,?,?)
     SELECT email_addr FROM list_email AS tmp
     WHERE email_addr NOT IN(
         SELECT email_addr FROM list_email WHERE email_addr = $post_addr)
         WHERE email_addr NOT IN (SELECT email_addr FROM list_no_email 
                                  WHERE email_addr = $post_addr)
     LIMIT 1;

Upvotes: 1

Related Questions