如果table2中不存在，如何插入表1中? [英] How to insert into table 1 if not exists in table2?

问题描述

我是mysql的新手.如果在table2中不存在将记录插入到table1中的问题，我有以下形式的2个表table1和table2:

I am new to mysql. I have a problem in inserting record to table1 if it does not exist in table2.I have 2 tables table1 and table2 in the form:

table1

dep_id  start     stop     modified deleted                 
1       23456789  167921525   Yes      No
2       34567812  345678145   Yes      No
3       32789054  327890546   No       No


table2

start     stop     modified deleted                 
23456789  167921525  No       No
34567823  345678145  No       No
32789053  727890546  No       No

仅当表2的开始"和停止"列中不存在值时，我才尝试将值插入到表1的开始和停止字段值中.如果存在，我需要抛出一个错误. 这些表没有主键外键关系. 抱歉，我不知道正确的语法，但是我必须在mysql和PHP中执行类似的操作.

I am trying to insert values into table1's start and stop field values only if it does not exist in table2's "start" and "stop" columns. If it exists the I need to throw an error. These tables do not have a primary key foreign key relationship. I apologize for not knowing correct syntax but I have to do something like this in mysql and PHP.

Replace Into into table1 set 'start'=> $start,'stop' => $stop
(select 'start','stop' from table2 where table1.start and table1.stop not in table2.start and table2.stop);

在插入到table1之前，如何查询这两个表以检查Table1.start和Table1.stop字段是否与Table2.start和Table2.stop不匹配?

How do I query these 2 tables to check if Table1.start and Table1.stop fields do not match with Table2.start and Table2.stop before inserting to table1?

推荐答案

您可以这样做:

INSERT INTO table1 (start, stop)
SELECT    a.*
FROM      (SELECT 123456789 start, 234567890 stop) a
LEFT JOIN table2 b ON (a.start,a.stop) IN ((b.start,b.stop))
WHERE     b.start IS NULL

其中123456789和234567890分别是start和stop的输入值.

Where 123456789 and 234567890 are your input values for start and stop respectively.

然后，您可以根据所使用的数据库接口(PDO，mysqli等)，使用rowCount或num_rows_affected对其进行检查.如果为0，则不会插入任何记录，否则，将发生插入.

Then you can check it with rowCount or num_rows_affected based on what DB interface you're using (PDO, mysqli, etc.). If it's 0, then no record was inserted, otherwise, the insert occurred.

SQLFiddle演示

这篇关于如果table2中不存在，如何插入表1中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！