将两列从不同的表合并到一起

Let's say for instance:

例如:假设

I have two tables: old_data and new_data.

我有两个表:old_data和new_data。
Both old_data and new_data have one column called this_is_col.

old_data和new_data都有一个列叫做this_is_col。
Both old_data and new_data have various (hundreds) of rows of dates (2010-02-06, 2010-01-09, 2007-06-02, etc.). Both tables don't necessarily have the same dates, but they both have the same format.

old_data和new_data都有不同的(数百)行日期(2010-02-06,2010-01-09,2007-06-02等)。两个表不一定有相同的日期，但是它们都有相同的格式。
The fields of both tables are various integers.

两个表的字段都是不同的整数。

My task:

我的任务:

Copy the fields from old_data to new_data.

将字段从old_data复制到new_data。
If a date exists in both tables, the field in new_data will be replaced.

如果两个表中都存在日期，则将替换new_data中的字段。
If the date doesn't exist in new_data, then the correct row will be added and the field will be copied over.

如果new_data中不存在日期，那么将添加正确的行，并复制字段。

Here is how far I've gotten:

这是我所取得的进展:

Create a temporary column:

创建一个临时的列:

ALTER TABLE `new_data` ADD `tempColumn` TEXT NULL;

Copy over data from old_data:

从old_data复制数据:

INSERT INTO `new_data` (`tempColumn`) SELECT `this_is_col` FROM `old_data`;

Combine temporary column and new_data . this_is_col. (I haven't really figured this step out since I haven't gotten this far).

合并临时列和new_data。this_is_col。(这一步我还没想出来，因为我还没走到这一步)。

MERGE? `tempColumn` `this_is_col`;

Delete temporary table

删除临时表

ALTER TABLE `new_data` DROP `tempColumn`;

Upon performing the second action (transferring the data over to the temporary column) I get this error:

在执行第二个操作(将数据转移到临时列)时，我得到这个错误:

#1062 - Duplicate entry '0000-00-00' for key 1

#1062 -重复输入“0000-00-00”作为键1

And now I'm stuck. Any help would be appreciated. I'm using MySQL and phpMyAdmin to test the SQL commands.

现在我被困住了。如有任何帮助，我们将不胜感激。我使用MySQL和phpMyAdmin来测试SQL命令。

4 个解决方案

#1

Assuming your dates are indexed as unique keys:

假设您的日期被索引为唯一键:

INSERT INTO newtable
SELECT *
FROM oldtable
ON DUPLICATE KEY column1=oldcolumn1, ...

#2

you want INSERT ... ON DUPLICATE KEY UPDATE. your solution already satisfies steps 1 and 3 of your task, ON DUPLICATE KEY UPDATE will take care of step 2.

你想要插入……在重复键更新。您的解决方案已经满足了任务的步骤1和步骤3，在重复密钥更新时将处理步骤2。

#3

If you'd rather delete the row first, instead of updating: REPLACE

如果您希望先删除行，而不是更新:REPLACE

It'd be just one line too, so: REPLACE data SELECT, you wouldn't have to do the weirdness with adding a text column.

它也只有一行，所以:替换data SELECT，您不需要添加文本列来做奇怪的事情。

#4

How about just doing an UPDATE and INSERT?

更新和插入怎么样?

UPDATE new_data SET col=col 
FROM new_data a join old_data b on a.this_is_col = b.this_is_col

Then

然后

INSERT INTO new_data (cols) SELECT cols 
FROM old_data WHERE this_is_col NOT IN (SELECT this_is_col FROM new_data)

Unless I misunderstood...

除非我误解了……

#1