mysql对同一张表进行查询和赋值更新

今天药忘吃喽~ 2022-01-12 06:45 554阅读 0赞

题：获取姓名为张三 的用户的age，并将age赋值给姓名赵六的的用户。  
原始数据：  
![在这里插入图片描述][20190621123208794.png]  
一般的写法是：

UPDATE user 
    SET age = ( SELECT age FROM user WHERE name = '张三' ) 
    WHERE
    	name = '赵六';

这样的写法对Oracle和SQLServer 来说都是正确的。但是mysql会报一个错误 ：

![\[Err\] 1093 - You can't specify target table 'tb\_user' for update in FROM clause][Err_ 1093 - You can_t specify target table _tb_user_ for update in FROM clause]

原因是：mysql在from子句中遇到子查询时，先执行子查询并将结果放到一个临时表中，我们通常称它为“派生表”；临时表是没有索引、无法加锁的。

update时，会锁表，此时不能再select。所以会报错，

此时如果将结果集放入临时表，临时表不会上锁，所以，可以正常查询并更新。

正确的写法：

### 方法一： ###

UPDATE user 
    SET age = ( SELECT age  FROM ( SELECT * FROM user ) AS tb_temp WHERE name = '张三' ) 
    WHERE
    	name = '赵六'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70]  
查看数据证明赵六的年龄已经修改成12岁。  
![在这里插入图片描述][20190621123345763.png]

注： tb\_temp临时表,存放查询结果集，这样就能避开上面说的错误。

### 方法二： ###

1.  创建一个中间表：

create table   tmp as select name  newname,age newage from `user`;

![在这里插入图片描述][20190621131340124.png]

2.进行修改操作：

UPDATE user 
    SET age = ( SELECT age FROM tmp WHERE name = '张三' ) 
    WHERE
    	name = '赵六';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70 1]  
原始表的数据赵六已经修改为12岁  
![在这里插入图片描述][20190621131528980.png]  
3.删除中间表：

drop table tmp;

### 方法三：使用mysql建表关键字：TEMPORARY ###

首先，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。  
创建临时表的语法与创建表语法类似，不同之处是增加关键字TEMPORARY，如：

CREATE TEMPORARY TABLE 表名 (…. )

1.  创建临时表：

create TEMPORARY table  tmp as select name newname , age newage  from user;

1.  临时表只在当前连接有效，所以要查看表是否创建成功可以使用以下命令：

select *  from tmp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70 2]

1.  进行更新操作

UPDATE user 
    SET age = ( SELECT age FROM tmp WHERE name = '张三' ) 
    WHERE
    	name = '赵六';

原始表的数据赵六已经修改为12岁  
![在这里插入图片描述][20190621131902313.png]  
4. 更新操作完成以后，关闭数据库连接，再重新打开数据库连接，验证临时表是不是已经被删除掉了。

select *  from tmp;

![在这里插入图片描述][20190621130217399.png]

--------------------

### 以下内容为解决本人的小问题而做的验证和上述问题无关，只是本人的笔记 ###

问题：解决在一张表将同一条数据的某个字段数据更新到本条数据的另一个字段中

### 方法一： ###

1.  创建一个中间表：

create table   tmp as select name  newname,age newage from `user`;

![在这里插入图片描述][20190621124555545.png]  
2.进行修改操作：

UPDATE user s,tmp t SET s.age=t.newname WHERE s.name=t.newname;

![在这里插入图片描述][20190621124620604.png]  
3.删除中间表：

drop table tmp;

### 方法二：使用mysql建表关键字：TEMPORARY ###

CREATE TEMPORARY TABLE 表名 (…. )

1.  创建临时表：

create TEMPORARY table  tmp as select name newname , age newage  from user;

1.  临时表只在当前连接有效，所以要查看表是否创建成功可以使用以下命令：

select *  from tmp;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70 3]

1.  进行更新操作

UPDATE user s,tmp t SET s.age=t.newname WHERE s.name=t.newname;

1.  更新操作完成以后，关闭数据库连接，再重新打开数据库连接，验证临时表是不是已经被删除掉了。

select *  from tmp;

![在这里插入图片描述][20190621130217399.png]

[20190621123208794.png]: /images/20220109/33d77aed0d5d46df85743bce4956c31a.png
[Err_ 1093 - You can_t specify target table _tb_user_ for update in FROM clause]: /images/20220109/993e30e2f52343869a3433d251b98f59.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70]: /images/20220109/e430935106804f60b17ce3470e1cf8bb.png
[20190621123345763.png]: /images/20220109/31a87435d1c94526b0fd48e011ae3edd.png
[20190621131340124.png]: /images/20220109/b11364606dd5443cb5a6f787abd03298.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70 1]: /images/20220109/d30493d0f6e747ddb31ecf9eb604edab.png
[20190621131528980.png]: /images/20220109/e39a45927a9145d798c5330fb38588d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70 2]: /images/20220109/d9db889d21694752bd97ad7db2fa3ebc.png
[20190621131902313.png]: /images/20220109/13615e204d0b4e4ab214dc864bd65a4e.png
[20190621130217399.png]: /images/20220109/42a3c2ba3087464fb8c0097eec1f6b8a.png
[20190621124555545.png]: /images/20220109/bb95f7e14cd043d588429147a28bd480.png
[20190621124620604.png]: /images/20220109/13657f3775044a3ea56772dbccb418d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dldGVyX2Ryb3A_size_16_color_FFFFFF_t_70 3]: /images/20220109/f76ed1ee5650487b9ab7d5e3df1771c1.png