本文介绍了MySQL INSERT INTO具有双重条件，如果不存在的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如果电子邮件地址在list_email.email_addr中不存在，并且在list_no_email.email_addr中不存在，我正在尝试插入新记录
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
推荐答案
替换
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
这篇关于MySQL INSERT INTO具有双重条件，如果不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！