当前位置:首页 > 技术分享 > 正文内容

mysql update不支持set子查询更新 的解决办法

admin2年前 (2020-09-02)技术分享990

先看示例:

SELECT uin,account,password,create_user_uin_tree FROM sys_user

结果:



表中的create_user_uin_tree标识该条记录由谁创建。

创建新用户时,根据当前登录用户的uin及新创建的用户uin,有如下SQL:

select concat(ifNULL(create_user_uin_tree,concat('_',2,'_')),'|_','97',"_")  from sys_user where uin=2

结果:



那么修改的create_user_uin_tree的标识SQL为:

update sys_user set create_user_uin_tree=(select concat(ifNULL(temp.create_user_uin_tree,concat('_',2,'_')),'|_','97',"_")  from sys_user temp where temp.uin=2) where uin = 97;

报错信息:

Error Code: 1093. You can't specify target table 'sys_user' for update in FROM clause 0.000 sec

原因

mysql中不支持子查询更新,准确的说是更新的表不能在set和where中用于子查询。那串英文错误提示就是说,不能先select出同一表中的某些值,再update这个表(在同一语句中)。 

两种方法:

1、select from 修改为子查询方式

2、inner join

方法1:子查询方式

调整了下SQL:

update sys_user set create_user_uin_tree=(select temp.tree from (select concat(ifNULL(create_user_uin_tree,concat('_',97,'_')),'|_','98',"_") as tree  from sys_user where uin=97) temp) where uin = 98;

我将作为子集,

select concat(ifNULL(create_user_uin_tree,concat('_',97,'_')),'|_','98',"_") as tree  from sys_user where uin=97) temp

然后再

select temp.tree from(子集)

 子集,这样就不会 select 和 update 都是同一个表。致此问题得到完美解决。

 

方法2:inner join

参考:

在sql server中,我们可是使用以下update语句对表进行更新:

update a set a.xx= (select yy from b) where a.id = b.id ;

但是在mysql中,不能直接使用set select的结果,必须使用inner join:

update a inner join (select yy from b) c on a.id =b.id  set a.xx = c.yy

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

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

错误提示是: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子句查询和保存都是在临时表里进行,然后间接地在外围查询被引用。下面的语句是正确的:

CREATE TABLE `user2` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(50) DEFAULT NULL,
  `device` VARCHAR(40) DEFAULT NULL,
  `memo` VARCHAR(40) DEFAULT NULL,  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT  INTO `user2`(`id`,`name`,`device`,`memo`) VALUES (1,'duan','11',NULL),(2,'liang','12',NULL),(3,'hou','13',NULL);

//子查询
UPDATE user2 SET memo=(SELECT temp.dc FROM (SELECT device AS dc FROM user2 WHERE id=2) temp) WHERE id = 2;
//列=列
UPDATE user2 SET memo=device;
//join

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

没有解决的问题

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

另一个没解决的问题是临时表被引用多次。“装进嵌套查询”的技巧无法解决这些问题,因为它们在编译时被创建,而上面讨论的update问题是在运行时。

 

另外一个示例:交换两条记录中的字段值方法

表test:  

    id         priority  

    1             1  

    2             2  

方法一:

mysql语句如下:

update question set sort=(case when id=7 then (select a.sort from (select tmp.* from question tmp) a
where a.id=8) when id=8  then (select a.sort from (select tmp.* from question tmp) a where a.id=7) end)
where id=7 or id=8;

方法二:

update question as q1 join question as q2 on (q1.id=7 and q2.id = 8)
or(q1.id = 8 and q2.id=7)
set q1.sort = q2.sort,q2.sort=q1.sort;


扫描二维码推送至手机访问。

版权声明:本文由小刚刚技术博客发布,如需转载请注明出处。

本文链接:http://blog.bitefu.net/post/61.html

标签: mysql
分享给朋友:

相关文章

apicloud开发调试方法 nodejs [超低,超省内存占用]

apicloud 开发 + 模拟器 +浏览器 +其它这些一开相当占用内存,让电脑不堪重负.于是我想到用命令行来实现调试这样可以不开启apicloud软件进行调试了 命令行+ 模拟器 就可以调试了准备1.检出项目.可以用svn检出,也可以直接...

节假日api 开源版 解密

介绍免费节假日API 开源了,可以离线调用, 可以集成到自己系统中开源地址https://gitee.com/web/holidays_api开源说明注:原百度节假日API即为本人提供,后百度apistore禁止个人开发者所以才有此独立接口...

linux centos svn搭建及网站同步更新

以前上线的项目都是用ftp来更新的.后来技术人员多了,ftp的弊端就出来了.所以在这里给大家介绍一下我们现在正在使用的svn同步修改更新的方案.原理很简单主要是用到了svn的hooks功能.即本地提交到服务器的svn服务器.然后svn服务器...

thinkphp等框架开发中容易忽略的xss攻击及应对XSS攻击方法

thinkphp等框架开发中容易忽略的xss攻击及应对XSS攻击方法

虽然说现在的web开发框架都是挺成熟的框架,在性能、安全等方面都有比较好的表现,但问题往往出现在业务逻辑上,如上周我再公司发现的一个跨站脚本攻击,(通常公司是这么过滤的,max(0,$_GET[‘a’])、strip_tags($_GET[...

WPS表格办公—取消科学计数法显示

WPS表格办公—取消科学计数法显示

我们在利用WPS表格与Excel表格进行日常办公时,经常需要制作各种各样的表格,当我们在表格当中输入长数据的时候,表格经常会自动显示为科学计数法,很多人都看不懂科学计数法的意思,那么,我们如何在输入长数字的时候避免显示为科学计数法呢,今天我...

centos 配置Let's Encrypt 泛域名https证书

centos 配置Let's Encrypt 泛域名https证书

前言2018年1月份Letsencrypt可以申请泛域名证书,这让我们部署多域名、多站点https省了很多功夫,终于可以不用维护多个域名的https证书。笔者以acme.sh为例,手把手教你配置https证书~本教程适用于centos 6....

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。