MySQL SELECT同时UPDATE同一张表

翻译 2013年02月19日 01:05:50
MySQL不允许SELECT FROM后面指向用作UPDATE的表,有时候让人纠结。当然,有比创建无休止的临时表更好的办法。本文解释如何UPDATE一张表,同时在查询子句中使用SELECT.

问题描述

假设我要UPDATE的表跟查询子句是同一张表,这样做有许多种原因,例如用统计数据更新表的字段(此时需要用group子句返回统计值),从某一条记录的字段update另一条记录,而不必使用非标准的语句,等等。举个例子:
create table apples(variety char(10) primary key, price int);

insert into apples values('fuji', 5), ('gala', 6);

update apples
    set price = (select price from apples where variety = 'gala')
    where variety = 'fuji';

错误提示是:ERROR 1093 (HY000): You can't specify target table 'apples' for update in FROM clause. MySQL手册UPDATE documentation这下面有说明 : “Currently, you cannot update a table and select from the same table in a subquery.”
在这个例子中,要解决问题也十分简单,但有时候不得不通过查询子句来update目标。好在我们有办法。

解决办法

既然MySQL是通过临时表来实现FROM子句里面的嵌套查询,那么把嵌套查询装进另外一个嵌套查询里,可使FROM子句查询和保存都是在临时表里进行,然后间接地在外围查询被引用。下面的语句是正确的:
update apples
   set price = (
      select price from (
         select * from apples
      ) as x
      where variety = 'gala')
   where variety = 'fuji';

如果你想了解更多其中的机制,请阅读MySQL Internals Manual相关章节。

没有解决的问题

一个常见的问题是,IN()子句优化废品,被重写成相关的嵌套查询,有时(往往?)造成性能低下。把嵌套查询装进另外一个嵌套查询里并不能阻止它重写成相关嵌套,除非我下狠招。这种情况下,最好用JOIN重构查询(rewrite such a query as a join)。

另一个没解决的问题是临时表被引用多次。“装进嵌套查询”的技巧无法解决这些问题,因为它们在编译时被创建,而上面讨论的update问题是在运行时。(译者注:个人认为跟文章讨论的主题没几毛钱关系)

转载请注明出处 http://blog.csdn.net/afeiqiang/article/details/8589535

原文地址 http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

mysql 各种级联查询后更新(update select).

mysql  各种级联查询后更新(update select).CREATE TABLE `tb1` (  `id` int(11) NOT NULL,  `A` varchar(100) defau...
  • wslyy99
  • wslyy99
  • 2009年07月10日 09:25
  • 19211

mysql下使用update set from select

最近遇到很多数据结构的需求是这样的: 先select出来 然后在update 一下  条件不变 写一条语句搞定: UPDATE tab_game_version  as a IN...
  • u010089432
  • u010089432
  • 2017年05月10日 09:52
  • 1508

mysql update select用法实例

http://www.3lian.com/edu/2014/05-13/147149.html 在mysql中,不能直接使用set select的结果,必须使用inner join: ...
  • A9925
  • A9925
  • 2017年01月29日 00:20
  • 1493

Select For update语句浅析

Select …forupdate语句是我们经常使用手工加锁语句。通常情况下,select语句是不会对数据加锁,妨碍影响其他的DML和DDL操作。同时,在多版本一致读机制的支持下,select语句也不...
  • liqfyiyi
  • liqfyiyi
  • 2012年09月05日 21:34
  • 142379

Select+Update并发处理

当一个会员想续买会员(只能续买1个月、3个月或6个月)时,必须满足以下业务要求: •如果end_at早于当前时间,则设置start_at为当前时间,end_at为当前时间加上续买的月数 •...
  • u011121287
  • u011121287
  • 2016年08月05日 10:17
  • 2557

关于update set from where

关于update set from where   下面是这样一个例子: 两个表a、b,想使b中的memo字段值等于a表中对应id的name值      表a:id,na...
  • xcbsdu
  • xcbsdu
  • 2011年08月31日 16:54
  • 95515

select for update学习总结

 1、select for update select for update 是对
  • zhan3107006604
  • zhan3107006604
  • 2014年04月14日 18:01
  • 2739

Update Select 嵌套更新或批量更新一句SQL语句搞定

有两个表:SPEC1、tmpDOT SPEC1种一列为:HIGH tmpDOT共有两个:OLD、NEW 两个表的内容大概如下: SPEC1: ... HIGH ... ... A    ....
  • ycb1689
  • ycb1689
  • 2015年02月15日 13:11
  • 18117

Select For Update行级锁定

Oracle的Select For Update语句可以实现在读取数据后马上锁定相关资源,防止被其他session修改数据的目的。也就是我们常常谈到的“悲观锁定”(现实应用开发中,使用悲观锁定的情况...
  • zhangshufa
  • zhangshufa
  • 2011年10月01日 22:36
  • 19284

Update Select批量更新

CREATE TABLE dualx( x_id VARCHAR(5) NOT NULL , x_con VARCHAR(10) ) CREATE TABLE ...
  • kanlianhui
  • kanlianhui
  • 2011年12月16日 17:47
  • 33894
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL SELECT同时UPDATE同一张表
举报原因:
原因补充:

(最多只允许输入30个字)