使用唯一值从一个表插入另一个表

I have a query to update Table 1 from table 2

我有一个查询来更新表2中的表1

Insert into Table1 (Column A, Column B, Column C,Column D)
Select Column A, Column B, Column C, Column D from Table 2

I want to run this is a cron to keep Table 2 updated as new records are added to Table 1. However I do NOT want duplicates as defined by Columns A-C.

我想运行这是一个cron,以便在表1中添加新记录时更新表2.但是我不希望列A-C定义的重复项。

I thought if I made a Unique Index in Table 2 using Column A, Column B, Column C it would simply not add new records unless they did not exist with those three columns but all I get is an error

我想如果我使用A列,B列,C列在表2中创建了一个唯一索引,它就不会添加新记录,除非它们不存在这三列但我得到的只是一个错误

Duplicate entry exists 'Column A-Column B-Column C' for key 'Unique'

密钥'Unique'存在重复条目'A列 - B列 - C列'

Is there anyway to set this up so that only new records are updated from the cron?

有没有设置它,以便只从cron更新新记录?

2 个解决方案

#1

You can use LEFT JOIN and insert only records that do not exists in table 1 based on your 3 columns:

您可以使用LEFT JOIN并根据您的3列仅插入表1中不存在的记录:

INSERT INTO Table1 (ColumnA, ColumnB, ColumnC,ColumnD)  
SELECT t2.ColumnA, t2.ColumnB, t2.ColumnC, t2.ColumnD 
FROM Table2 t2
LEFT JOIN Table1 t1
  ON t1.ColumnA = t2.ColumnA
 AND t1.ColumnB = t2.ColumnB
 AND t1.ColumnC = t2.ColumnC
WHERE t1.ColumnA IS NULL;

SqlFiddleDemo

I've assumed that ColumnA-C are compound PRIMARY KEY.

我假设ColumnA-C是复合PRIMARY KEY。

EDIT:

You can use MySQL INSERT IGNORE:

您可以使用MySQL INSERT IGNORE:

INSERT IGNORE INTO Table1 (ColumnA, ColumnB, ColumnC,ColumnD)  
SELECT t2.ColumnA, t2.ColumnB, t2.ColumnC, t2.ColumnD 
FROM Table2 t2;

SqlFiddleDemo2

If you need to update rest of columns with newer values you can use:

如果您需要使用较新的值更新其余列,您可以使用:

INSERT INTO Table1 (ColumnA, ColumnB, ColumnC,ColumnD)  
SELECT t2.ColumnA, t2.ColumnB, t2.ColumnC, t2.ColumnD 
FROM Table2 t2
ON DUPLICATE KEY UPDATE ColumnD = VALUES(ColumnD);

SqlFiddleDemo3

There is also REPLACE INTO (slower and will remove old record + insert new one):

还有REPLACE INTO(较慢并将删除旧记录+插入新记录):

REPLACE INTO Table1 (ColumnA, ColumnB, ColumnC,ColumnD)  
SELECT t2.ColumnA, t2.ColumnB, t2.ColumnC, t2.ColumnD 
FROM Table2 t2

SqlFiddleDemo4

#2

If any of your column has unique value (e.g. column A is your PRIMARY KEY) you can use:

如果您的任何列具有唯一值(例如,列A是您的PRIMARY KEY),您可以使用:

INSERT INTO table1 (Column_A, Column_B, Column_C, Column_D)
SELECT Column_A, Column_B, Column_C, Column_D 
FROM table2 
WHERE column_A NOT IN (
  SELECT column_A FROM table1
);

This statement will insert into table1 entries with values of Column_A of table2 that are not exists in Column_A of table1.

此语句将插入table1条目,其中table2的Column_A值不存在于table1的Column_A中。

#1