gpt4 book ai didi

具有 IF NOT EXIST 双重条件的 MySQL INSERT INTO

转载 作者:行者123 更新时间:2023-11-29 00:26:41 25 4
gpt4 key购买 nike

如果电子邮件地址不存在于 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/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com