作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
如果电子邮件地址不存在于 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
最佳答案
尝试这样的事情:
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;
关于具有 IF NOT EXIST 双重条件的 MySQL INSERT INTO,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18584541/
我是一名优秀的程序员,十分优秀!