blocks|key|5203937|text|使用LEFT+OUTER+JOIN也可以做到这一点。这将避免子查询开销(当系统可能为外部查询的每个记录执行子查询一次时)，就像John+Woo的答案中那样，并将避免像user2340435中的那样覆盖现有的800条记录的不必要的工作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5203938|INSERT+INTO+b
SELECT+a.*+FROM+a
LEFT+OUTER+JOIN+b+ON+b.id+=+a.id
WHERE+b.id+IS+NULL;|code-block|syntax|javascript|5203939|这将首先选择A和B表中的所有行，包括这两个表中的所有列，但是对于存在于A中而不存在于B中的行，B表的所有列都将为NULL。然后，它只过滤后面的行(WHERE+b.id+IS+NULL)，最后将所有这些行插入到B表中。|5203940|entityMap^0|2|F|0|0|6|1|8|1|Z|1|16|1|1B|1|1K|4|21|I|2W|1|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|N|$]]

It is also possible to use <code>LEFT OUTER JOIN</code> 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:

<pre><code>INSERT INTO b
SELECT a.* FROM a
LEFT OUTER JOIN b ON b.id = a.id
WHERE b.id IS NULL;
</code></pre>

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

blocks|key|3081667|text|我认为你可以使用IN来做这件事。(这是您的查询的简化)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3081668|INSERT+INTO+table2+(id,+name)
SELECT+id,+name
FROM+table1
WHERE+(id,name)+NOT+IN+
+++++++(SELECT+id,+name
++++++++FROM+table2);|code-block|syntax|javascript|3081669|SQLFiddle+Demo|3081670|正如您在演示中看到的，table2只有1条记录，但在执行查询后，在table2上插入了2条记录。|3081671|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/744755/2^0|8|2|0|0|0|E|0|0|B|6|X|6|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|D|@$9|11|A|12|1|13]]|E|$]]|$1|M|3|N|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

I think you can use <code>IN</code> for this. (this is a simpliplification of your query)

<pre><code>INSERT INTO table2 (id, name)
SELECT id, name
FROM table1
WHERE (id,name) NOT IN 
 (SELECT id, name
 FROM table2);
</code></pre>

<h1><a href="http://sqlfiddle.com/#!2/744755/2" rel="noreferrer">SQLFiddle Demo</a></h1>

AS you can see on the demonstration, <code>table2</code> has only 1 records but after executing the query, 2 records were inserted on <code>table2</code>.

blocks|key|3564911|text|如果是mysql，并且表是相同的，那么这应该是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3564912|REPLACE+INTO+table1+SELECT+*+FROM+table2;|code-block|syntax|javascript|3564913|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If it's mysql and the tables are identical, then this should work:

<pre><code>REPLACE INTO table1 SELECT * FROM table2;
</code></pre>

blocks|key|5294439|text|当使用group+by和having子句时，代码也可以工作。测试的SQL2012+(11.0.5058)+Tab1是具有新记录的源，选项卡2是要更新的目标。选项卡2也有一个Identity列。(是的，朋友们，现实世界并不像实验室作业那样整洁和干净)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5294440|INSERT+INTO+Tab2
SELECT++a.T1,a.T2,a.T3,a.T4,a.Val1,a.Val2,a.Val3,a.Val4,-9,-9,-9,-9,MIN(hits)+MinHit,MAX(hits)+MaxHit,SUM(count)+SumCnt,+count(distinct(week))+WkCnt
FROM+Tab1+a+
LEFT+OUTER+JOIN+Tab2+b+ON+b.t1+=+a.t1+and+b.t2+=+a.t2+and+b.t3+=+a.t3+and+b.t4+=+a.t4+and+b.val1+=+a.val1+and+b.val2+=+a.val2++and+b.val3+=+a.val3++and+b.val4+=+a.val4+
WHERE+b.t1+IS+NULL+or+b.Val1+is+NULL
group+by+a.T1,a.T2,a.T3,a.T4,a.Val1,a.Val2,a.Val3,a.Val4+having+MAX(returns)<4+and+COUNT(distinct(week))>2+;|code-block|syntax|javascript|5294441|entityMap^0|3|8|C|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Code also works when a <code>group by</code> and <code>having</code> clauses are used. Tested SQL 2012 (11.0.5058) Tab1 is source with new records, Tab 2 is the destination to be updated. Tab 2 also has an Identity column. (Yes folks, real world is not as neat and clean as the lab assignments) 

<pre><code>INSERT INTO Tab2
SELECT a.T1,a.T2,a.T3,a.T4,a.Val1,a.Val2,a.Val3,a.Val4,-9,-9,-9,-9,MIN(hits) MinHit,MAX(hits) MaxHit,SUM(count) SumCnt, count(distinct(week)) WkCnt
FROM Tab1 a 
LEFT OUTER JOIN Tab2 b ON b.t1 = a.t1 and b.t2 = a.t2 and b.t3 = a.t3 and b.t4 = a.t4 and b.val1 = a.val1 and b.val2 = a.val2 and b.val3 = a.val3 and b.val4 = a.val4 
WHERE b.t1 IS NULL or b.Val1 is NULL
group by a.T1,a.T2,a.T3,a.T4,a.Val1,a.Val2,a.Val3,a.Val4 having MAX(returns)&lt;4 and COUNT(distinct(week))&gt;2 ;
</code></pre>

blocks|key|5203999|text|这会将丢失的记录插入到Table1中|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5204000|INSERT+INTO+Table2
(Col1,+Col2....)
(
SELECT+++Col1,+Col2,...+FROM+Table1
EXCEPT
SELECT++Col1,+Col2,...+FROM+Table2
)|code-block|syntax|javascript|5204001|然后，可以运行更新查询以匹配不同的记录。|5204002|UPDATE+Table2
SET
Col1=+T1.Col1,
Col2=+T1.Col2,
FROM
Table+T1
INNER+JOIN
Table2+T2
ON
T1.Col1+=+T2.Col1|5204003|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This will insert the missing records into Table1

<pre><code>INSERT INTO Table2
(Col1, Col2....)
(
SELECT Col1, Col2,... FROM Table1
EXCEPT
SELECT Col1, Col2,... FROM Table2
)
</code></pre>

You can then run an update query to match the records that differ.

<pre><code>UPDATE Table2
SET
Col1= T1.Col1,
Col2= T1.Col2,
FROM
Table T1
INNER JOIN
Table2 T2
ON
T1.Col1 = T2.Col1
</code></pre>

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

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

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

In the end, both <code>Table A</code> and <code>Table B</code> should be identical.

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

Thank you.

Insert missing records from one table to another using mysql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我不知道为什么我会对这个查询感到困惑。我有两个表:包含900记录的Table A和包含800记录的Table B。这两个表需要包含相同的数据，但存在一些不匹配。我需要编写一个mysql查询来将缺少的100记录从Table A插入到Table B。最后，Table A和Table B应该是相同的。我不想先截断所有条目，然后再从另一个表执行插入操作。所以请多多帮助，我们将不胜感激。谢谢。

问使用mysql将丢失的记录从一个表插入到另一个表
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用mysql将丢失的记录从一个表插入到另一个表EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用mysql将丢失的记录从一个表插入到另一个表
EN