13

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 "

What I have in 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) 

2012-10-31 
2012-09-31 
2012-07-31 
2012-10-31 
2012-11-31

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.

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 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 ...);
Community
  • 1
  • 1
ekta
  • 1,560
  • 3
  • 28
  • 57
  • A quick way to use to get the date , instead of substring_index is select extract(minute from timestamp '2009-09-09 12:08:43'); , while still using the self joins etc. – ekta Apr 03 '15 at 07:33

2 Answers2

26

Use a SELF JOIN, like this:

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

Aziz Shaikh
  • 16,245
  • 11
  • 62
  • 79
  • Does not work :( ERROR 1242 (21000): Subquery returns more than 1 row – ekta Jan 20 '14 at 06:56
  • @ekta I have modified my answer above, now the query uses SELF JOIN instead of subqueries. I have also added a link to sql fiddle for a working demo. You may try the updated query. – Aziz Shaikh Jan 20 '14 at 06:58
  • Yes, I just noticed that and ran your new query above - here's what I now see - " ERROR 1265 (01000): Data truncated for column 'date_custom' at row 1 " I also extended the where clause to include all possible id's that uniquely make up a row. What am I doing wrong ? UPDATE tab_1 t1, tab_1 t2 SET t1.date_custom = SUBSTRING_INDEX(t2.DT," " ,1) WHERE t1.DT = t2.DT and t1.AUCTION_ID_64=t2.AUCTION_ID_64 and t1.USER_ID_64=t2.USER_ID_64 ; – ekta Jan 20 '14 at 07:02
  • your SQL fiddle works and the table is quite close to what I have, except the primary key - should I try creating a column with auto indexing primary key. But in any case - I would like to know why this FAILS ? – ekta Jan 20 '14 at 07:06
  • @ekta your updated query looks ok. The error you are getting is probably due to incompatible datatype of the column 'date_custom' and the data that is being inserted into that column. Having (or not having) a primary key is irrelevant to this issue. – Aziz Shaikh Jan 20 '14 at 07:09
  • You were right - I mistakenly overwrote the data type as int instead of Varchar while trying your suggestions, the second time around. Thanks and +1 – ekta Jan 20 '14 at 07:27
9

Just one little modification to the accepted answer:

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

You can use UPDATE without SELF JOIN

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

That improve the performace 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

MTK
  • 3,300
  • 2
  • 33
  • 49