仅当存在另一个表的ID时才插入行 [英] Insert rows only when id from another table exists

问题描述

我的程序获取一个ID列表，其中一些在我们的系统中，而有些则不在.目标是在表中输入系统中存在的ID，但忽略不存在的ID.

My program gets a list of IDs, some of which are in our system, some of which are not. The goal is to make a entry into a table for the IDs which exist in our system but ignore those which do not.

示例:

Incomming    My System    Inserted
555          583          583
583
599

我最初的想法是使用INSERT IGNORE，但是要检查的ID列表位于users表中，并且将行插入到事件表中.我还尝试了外键约束，但INSERT IGNORE不会因FK默默地失败-它会产生一个错误，该错误会杀死随后的所有插入.

My initial thought was to use INSERT IGNORE but the list of ID's to check against are in the users table, and the rows are being inserted to the events table. I also tried a Foreign Key constraint but INSERT IGNORE doesn't fail silently with a FK - it produces an error which kills any following inserts.

我当前的攻势是从用户表中读取所有ID到PHP，并在尝试插入之前检查PHP中的ID，但这似乎不是最佳选择.有MySQL的方法吗?

My current angle of attack is to read in all the IDs from the users table to PHP, and check the IDs in PHP before attempting to insert, but this seems sub-optimal. Is there a MySQL way to do it?

推荐答案

INSERT INTO your_id_table (ID) 
SELECT ID FROM my_system_table WHERE ID IN (list_of_ids)

我希望它很清楚，您根本不需要进行任何检查，您只需运行查询即可完成操作.

I hope its clear, with this you don't have to do any check at all, you just run the query and be done with it.

这篇关于仅当存在另一个表的ID时才插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！