mysql update不支持子查询更新的解决办法【不能先select出同一表中的某些值，再update这个表(在同一语句中) 】

mysql update不支持子查询更新【mysql-不能先select出同一表中的某些值，再update这个表(在同一语句中)】

报错提示：

1093 - You can't specify target table 'a' for update in FROM clause

方法一：inner join

-- mysql update不支持子查询更新 解决办法
-- 更新
update mini_goods_type a inner join(select b.goods_type_id,b.`name` from mini_goods_type b where b.type=1 and b.parent_id is null) c on a.parent_id=c.goods_type_id and a.type=1 and a.parent_id is not null and a.parent_id='4028826a77d31f7a0177d324d1f00000' set a.parent_name=c.name;
-- 查询
select * from mini_goods_type a inner join(select b.goods_type_id,b.`name` from mini_goods_type b where b.type=1 and b.parent_id is null) c on a.parent_id=c.goods_type_id and a.type=1 and a.parent_id is not null and a.parent_id='4028826a77d31f7a0177d324d1f00000';

mysql根据关联表对自身更新

update A inner join(select id,name from B) c on A.id = c.id set A.name = c.name;

-- mysql update不支持子查询更新 解决办法
update mini_goods_type a inner join(select b.goods_type_id,b.`name` from mini_goods_type b) c on a.parent_id=c.goods_type_id set a.parent_name=c.name;

方式二：子查询方式

update mini_goods_type a set a.has_child=(select temp.count from (select count(goods_type_id) as count from mini_goods_type where parent_id='4028826a77d31f7a0177d324d1f00000') temp) where goods_type_id='4028826a77d31f7a0177d324d1f00000';

update sys_user d set d.role_name='经办人' where d.id in(select temp.id from (select a.id from sys_user a,sys_user_role b,sys_role c where a.type=2 and a.id=b.user_id and b.role_id=c.id and c.`name`='操作员') temp);

//只能用sql了

		String sql = "update mini_goods_type " +
				" set has_child=" +
					"(" +
						"select temp.count from (" +
						"select count(goods_type_id) as count " +
						" from mini_goods_type " +
						" where parent_id=?) temp" +
					") " +
				"where goods_type_id=?"
				;
		this.excuteUpdateForSQL(sql, new Object[]{ id, id });

以下HQL实测不行 会报错：“node to traverse cannot be null!”

String hql = "update GoodsType " +
				" set hasChild=" +
					"(" +
						"select temp.count from " +
						"(select count(goodsTypeId) as count " +
						" from GoodsType " +
						" where parentId=?) temp" +
					")" +
				" where goodsTypeId=? "

参考文献：

https://www.cnblogs.com/duanxz/p/5099030.html

mysql-不能先select出同一表中的某些值，再update这个表(在同一语句中)_不能先select出同一表中的某些值,再update这个表-CSDN博客