当前位置: 动力学知识库 > 问答 > 编程问答 >

Mysql 5.5 to 5.7 - You can't specify target table for update in FROM clause - Same Table

问题描述:

Some queries that I had written for MySQL 5.5 no longer work for MySQL 5.7. I'm not sure if there is a configuration setting...

The following query is no longer valid:

Update Tablex set Column1 = 3 where Tablex.ID = 20 and not exists (

Select * From ( Select * from Tablex a where a.StartTime > :Start and a.EndTime <:end ) as DerivedTable1

)

//You can't specify target table Tablex for update in FROM clause

This worked perfectly in MySQL 5.5, basically the idea is that MySQL would create a temp table...but now I guess not. Anyone know how to update this for MySQL 5.7? Alternatively I can use a transaction and pre-query the exists condition and then proceed based on the results.

网友答案:

That's indeed a change in MySQL 5.7, described in the docs:

The optimizer now handles derived tables and views in the FROM clause in consistent fashion to better avoid unnecessary materialization and to enable use of pushed-down conditions that produce more efficient execution plans. However, for statements such as DELETE or UPDATE that modify tables, using the merge strategy for a derived table that previously was materialized can result in an ER_UPDATE_TABLE_USED error.

The error occurs when merging a derived table into the outer query block results in a statement that both selects from and modifies a table. (Materialization does not cause the problem because, in effect, it converts the derived table to a separate table.) To avoid this error, disable the derived_merge flag of theoptimizer_switch system variable before executing the statement:

mysql> SET optimizer_switch = 'derived_merge=off';

Another possible workaround is mentioned in this thread:

  • Force the derived table to be materialized, e.g by adding DISTINCT after SELECT, or adding a LIMIT.

This approach has been chosen by Rails developers to fix the similar bug.

网友答案:

The workaround referenced in the answer from raina77ow is correct. However, not being able to execute this particular kind of query UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...); is a bug that was introduced in 5.7.6 and fixed in 5.7.11.

Bug report: https://bugs.mysql.com/bug.php?id=79333

5.7.11 release notes (search for 79333).

网友答案:

You can do this all in one query, using a join:

Update Tablex x left join
       Tablex x2
       on x2.id = x.id and x2.StartTime > :Start and x2.EndTime < :end
    set x.Column1 = 3
    where x.ID = 20 and x2.id is null;

This should work in both MySQL 5.5 and 5.7.

网友答案:

@raina77ow is right.

you also can add a row at /etc/my.cnf

optimizer_switch=derived_merge=off

ps: I use rails, so I can't change the default behavior of rails.

网友答案:

I solved that by upgrading from 5.7.9 to the latest version now as I speak is 5.7.12 and the subquery workaround started working again.

分享给朋友:
您可能感兴趣的文章:
随机阅读: