MySQL INSERT INTO具有双重条件，如果不存在

小编典典

MySQL INSERT INTO具有双重条件，如果不存在

sql

如果电子邮件地址在list_email.email_addr中不存在且在list_no_email.email_addr中不存在，我正在尝试插入新记录

INSERT INTO list_email(fname, lname, email_addr) VALUES('bob', 'schmoe', 'bogus@bogus.com'), ('mary', 'lamb', 'hoe@me.com');
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)

*_ __ _ __ __ * 错误 * __ __ *
*

INSERT INTO list_email(fname, lname, email_addr) VALUES('bob', 'schmoe', 'bogus@bogus.com'), ('mary', 'lamb', 'hoe@me.com'); 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

*_ __ _ __ __ 不带; __ * __ __ * __ **

mysql> INSERT INTO list_email(fname, lname, email_addr) VALUES('bob', 'schmoe', 'bogus@bogus.com'), ('mary', 'lamb', 'hoe@me.com')
    -> 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

阅读 269

2021-04-14

共1个答案

小编典典

代替

WHERE NOT EXIST(

和

WHERE email_addr NOT IN(

编辑

代替

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

和

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

2021-04-14