使用mysql将丢失的记录从一个表插入到另一个表 [英] Insert missing records from one table to another using mysql

问题描述

我不知道为什么对这个查询感到困惑.

I don't know why I am confused with this query.

我有两个表:带有900记录的Table A和带有800记录的Table B.两个表都需要包含相同的数据，但是存在一些不匹配.

I have two table: Table A with 900 records and Table B with 800 records. Both table need to contain the same data but there is some mismatch.

我需要编写一个mysql查询，以将丢失的100记录从Table A插入到Table B.

I need to write a mysql query to insert missing 100 records from Table A to Table B.

最后，Table A和Table B应该相同.

我不想先截断所有条目，然后再从另一个表中插入.因此，请提供任何帮助.

I do not want to truncate all the entries first and then do a insert from another table. So please any help is appreciated.

谢谢.

推荐答案

也可以使用LEFT OUTER JOIN.这将避免像John Woo的回答那样避免子查询的开销(当系统可能对外部查询的每个记录执行一次子查询时)，并且避免进行不必要的工作来覆盖已经存在的800条记录，例如user2340435的那条记录: /p>

It is also possible to use LEFT OUTER JOIN for that. This will avoid subquery overhead (when system might execute subquery one time for each record of outer query) like in John Woo's answer, and will avoid doing unnecessary work overwriting already existing 800 records like in user2340435's one:

INSERT INTO b
SELECT a.* FROM a
LEFT OUTER JOIN b ON b.id = a.id
WHERE b.id IS NULL;

这将首先选择A和B表中的所有行，包括两个表中的所有列，但对于A中存在但在B表中不存在的所有行将为NULL. 然后，它仅过滤后面的这些行(WHERE b.id IS NULL)， 最后将所有这些行插入B表中.

This will first select all rows from A and B tables including all columns from both tables, but for rows which exist in A and don't exist in B all columns for B table will be NULL. Then it filter only such latter rows (WHERE b.id IS NULL), and at last it inserts all these rows into B table.

这篇关于使用mysql将丢失的记录从一个表插入到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！