MySql 如何将 UPDATE 与 HAVING 一起使用? [英] MySql how to use UPDATE with HAVING?
本文介绍了MySql 如何将 UPDATE 与 HAVING 一起使用?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的 table1 看起来像:<预>
id name_co name_r temp sld1 姓名1 1 ... ...2 名称2 1 ... ...3 名称2 1 ... ...4 名称2 1 ... ...5 名称3 1 ... ...6 名称2 1 ... ...
如果有两个或更多相同的 name_co，我需要增加 name_r.如此:
我尝试了不同的选择，结果是:
UPDATE table1SET name_r = name_r + 1哪里(选择计数(*)GROUP BY name_coHAVING name_co >1)查询有效并返回 0 行，但我知道他在某种程度上是错的，但我不知道是什么.任何人都可以帮忙吗?(还有一点解释，让我更好地理解了)
解决方案
--更新了预期目标更新表1SET name_r = name_r + 1在哪里(-- 再次返回这些 id(以避免 mysql #1093 错误)选择 ID从( -- 获取这些名称的所有 ID选择 ID从表 1WHERE name_co IN( -- 获取所有具有多个 id 的名称选择名称_co从表 1GROUP BY name_co有 COUNT(id) >1)) 一种)My table1 looks like:
id name_co name_r temp sld 1 name1 1 ... ... 2 name2 1 ... ... 3 name2 1 ... ... 4 name2 1 ... ... 5 name3 1 ... ... 6 name2 1 ... ...
I need to increment name_r if there are two or more identical name_co.
To be so:
id name_co name_r temp sld 1 name1 1 ... ... 2 name2 1 ... ... 3 name2 2 ... ... 4 name2 3 ... ... 5 name3 1 ... ... 6 name2 4 ... ...
I tried different options and I came to this:
UPDATE table1
SET name_r = name_r + 1
WHERE (SELECT COUNT(*)
GROUP BY name_co
HAVING name_co > 1)
The query works and returns 0 rows, but I know that in some way he's wrong, but I can't figure out what. Can anyone help? (And a bit of explanation, so I better understood)
解决方案
--updated intended targets
UPDATE table1
SET name_r = name_r + 1
WHERE id IN
(
-- return those ids again (to avoid the mysql #1093 error)
SELECT id
FROM
( -- get all the ids for those names
SELECT id
FROM table1
WHERE name_co IN
( -- get all names that have more than one id
SELECT name_co
FROM table1
GROUP BY name_co
HAVING COUNT(id) > 1
)
) a
)
这篇关于MySql 如何将 UPDATE 与 HAVING 一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
