从mysql中的另一列更新一个派生值的列

I am trying to update the value of a column from another column in the same tabl e- but this fails with " ERROR 1093 (HY000): You can't specify target table 'tab_1' for update in FROM clause "

我正在尝试更新同一个tabl中另一列的列值 - 但是这个失败了“ERROR 1093（HY000）：你不能在FROM子句中指定目标表'tab_1'进行更新”

What I have in Mysql

我在Mysql中有什么

DT;                  date_custom
2012-10-31 17:00:22; 0
2012-09-31 17:00:21; 0
2012-07-31 17:00:25; 0
2012-10-31 17:43:56; 0
2012-11-31 17:44:09; 0

what I need in the corresponding date_custom field(column)

我在相应的date_custom字段（列）中需要什么

In other words, I just want Mysql to pick up the corresponding row for column DT and just DUMP the derived value date_column. This should be on a one-one basis. I do have a combination of keys that uniquely identify a row, but I don't want to use it if I can identify that.

换句话说，我只是希望Mysql为DT列选取相应的行，而只需要DUMP派生值date_column。这应该是一对一的。我确实有一个唯一标识行的键组合，但如果我能识别出来，我不想使用它。

Here's what I tried and did not work .

这是我尝试过但没有用的东西。

Before this I created this column - date_custom as below -:
alter table tab_1
add column date_custom int not null;

# simplistic
UPDATE tab_1 SET date_custom = (SELECT SUBSTRING_INDEX(DT," " ,1) FROM tab_1);

I am also aware that I can't modify a column at the same time, while trying to access that - but since this is different columns, things should not fail here, right - or what am I doing wrong ?

我也意识到我不能同时修改一个列，同时尝试访问它 - 但由于这是不同的列，所以事情不应该在这里失败，对 - 或者我做错了什么？

# using self joins on subquery
UPDATE tab_1
SET tab_1.date_custom =
(
    SELECT SUBSTRING_INDEX(a.DT," " ,1)
    FROM tab_1 a
    INNER JOIN tab_1 b on  
    a.DT = b.DT and a.AUCTION_ID_64=b.AUCTION_ID_64 # these 2 columns together make up the primary key, but I would like to avoid using this if possible
) # does not work

This corresponds to the thread here You can't specify target table for update in FROM clause

这对应于此处的线程您无法在FROM子句中指定要更新的目标表

**From the official documentation - "In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:" **

**从官方文档 - “通常，您不能修改表并从子查询中的同一个表中进行选择。例如，此限制适用于以下形式的语句：”**

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:
UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);

2 个解决方案

#1

Use a SELF JOIN, like this:

使用SELF JOIN，如下所示：

UPDATE test t1, test t2 
SET t1.date_custom = SUBSTRING_INDEX(t2.dt," " ,1)
WHERE t1.id = t2.id

Working Demo: http://sqlfiddle.com/#!2/9b71cb/1/0

工作演示：http：//sqlfiddle.com/#！2/9b71cb/1/0

#2

Just one little modification to the accepted answer:

对接受的答案只做一点修改：

With a big table the SELF JOIN can take a lot of time to process

使用大表，SELF JOIN可能需要花费大量时间来处理

You can use UPDATE without SELF JOIN

您可以在没有SELF JOIN的情况下使用UPDATE

UPDATE test t1
SET t1.date_custom = SUBSTRING_INDEX(t1.dt," " ,1);

That improve the performace DRASTICALLY

这可以提高DRASTICALLY的性能

Tested a similar query with SELF JOIN 26000 rows not finish yet in two hours (imagine one with milions entryes !!! ) but without SELF JOIN the same query take less than 2 seconds

用SELF JOIN测试了类似的查询26000行还没有在两个小时内完成（想象一个有milions入口!!!）但没有SELF JOIN相同的查询需要不到2秒

#1