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，并且我没有找到其他方法来实现这一点。

有任何想法吗？

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 

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

Answer 2

更新后立即使用连接：
参考手册 – 13.2.11 UPDATE 语法

更新表 1 内连接表 2 ....
设置 table1.foo=值
where table2.bla = someothervalue

对于此类事情，请务必查看手册。 MySql 有适当的参考手册，因此获得正确的语法应该不难；）

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

Answer 4

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

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

最终起作用的是在目标表上添加另一个“哪里存在选择”。

我正在更新 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

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