MySQL/SQL: 使用相关子查询从更新后的表本身进行更新

Question

我有一个通用的问题，我将尝试使用一个例子来解释。

假设我有一个表，其中包含字段："id"、"name"、"category"、"appearances"和"ratio"

我的意思是，我有多个项目，每个项目都与一个单一的类别相关联，并且会出现多次。比率字段应该包括每个项目出现在类别中的出现次数占所有项目出现次数的百分比。

用伪代码表示，我需要以下内容：

• 对于每个类别
  找到与之相关的项目的出现总和。例如，可以通过 (select sum("appearances") from table group by category) 完成。

• 对于每个项目
  将比率值设置为该项目的出现次数除以上述类别发现的总和

现在我正在尝试使用单个更新查询实现此目的，但似乎无法做到。我认为我应该做的是：

update Table T    
set T.ratio = T.appearances /   
(    
select sum(S.appearances)    
from Table S    
where S.id = T.id    
)

但是MySQL不接受在更新列中使用别名T，我也没有找到其他实现这一点的方法。有什么想法吗？

- Roee Adler

Answer 1

在收到两个答案后（都不完整，所以我自己写了一个），我最终做的如下：

UPDATE Table AS target
INNER JOIN 
(
select category, appearances_sum
from Table T inner join (
    select category as cat, sum(appearances) as appearances_sum
    from Table
    group by cat
) as agg
where T.category  = agg.cat
group by category
) as source
ON target.category = source.category
SET target.probability = target.appearances / source.appearances_sum 

它的工作速度非常快。我也尝试了相关子查询，但速度要慢得多（数量级），所以我坚持使用连接。

- Roee Adler

---

请将一个答案标记为正确答案，这样问题就会从未回答的问题列表中移除 :) - Frans Bouma

@Frans：我必须等待48小时才能这样做，堆栈溢出规则 :) - Roee Adler

很好。感谢您抽出时间提供完整的示例！ - Ben

遇到了相同的问题，谢谢！我用内连接解决了它。 - Packet Tracer

你觉得你能帮我解决我的问题吗？它似乎是一个类似的问题。谢谢！http://stackoverflow.com/questions/17070502/update-annual-changes-with-discontinuous-dates - user1382306

Answer 2

在 UPDATE 后面使用 JOIN：

参考手册 - 13.2.11 UPDATE 语法

因此，可以这样写： UPDATE table1 INNER JOIN table2 ON ... SET table1.foo=value WHERE table2.bla=someothervalue

对于这些内容，始终要查看手册。MySql 有一个适当的参考手册，因此获取正确的语法不应该很难；）

- Frans Bouma

---

谢谢，我会尽快尝试。顺便说一句-在发布问题之前，我确实阅读了文档并尝试了所有有意义的方法 :) - Roee Adler

谢谢，手册中的条目确实为我解决了问题。你也做得很好。 - Packet Tracer

Answer 3

这是在mssql中完成的方法，我认为mysql也是相同或类似的：

create table T (id int, ratio float, appearances int)
insert T values (1, null, 2)
insert T values (1, null, 3)

update T
set ratio = cast(appearances as float)/ agg.appearancesSum
from T join (
    select id, sum(appearances) as appearancesSum
    from T
    group by id
) as agg on t.id = agg.id

- ahains

---

抱歉，这在MySQL中不起作用。我没有将其投票降低，因为我确信它在mssql上能够正常工作... - Roee Adler

Answer 4

这是一个老问题，但我认为人们仍在关注它。我查看了首选答案，但继续为我的MariaDB/MySQL数据库工作相关子查询。相关子查询是我最不喜欢组合的，因为它们总是在我的脑海中有意义，直到我尝试配置一个。哈哈

我尝试了几种相关子查询的变体，更新了表中所有记录或具有相同公共键的所有记录。

最终起作用的是在目标表上添加另一个“where exists select”。

我正在更新2个加密记录，这些记录从同一张表中具有有效加密字段的记录中损坏了。将其剪切并粘贴到标准更新语句中无效。

根据更新的行数，我运行回滚或提交，直到得到更新预期2行的子查询。

start transaction;

update account tar
   set value =  (select value 
                 from account src
                 Where src.name = tar.name
                   and src.name = 'acct_no'
                   and src.customAttributesId = '8adac1537dbe5c39017dc89f082e0341') 
    Where exists (select 1
                  from account
                  where tar.name = 'acct_no'
                   and tar.customAttributesId in 
                 ('8adac1537dbe5c39017dc8a0e5db0610', 
                  '8adac1537dbe5c39017dc8a0e491060d')
                );

Rollback;
Commit;

15:43:16    update account tar    set value =  (select value                   from)    2 row(s) affected Rows matched: 2  Changed: 2  Warnings: 0  0.141 sec

我有另一个进程确认更新字段中加密的账号是有效的。希望这可以帮到你。

- Hawkman2k