MySql 将嵌套的 SELECT IN 查询转换为 JOIN

Question

我读到在 MySql 查询中使用 IN 会导致性能下降，这与在 Oracle 中不同，应该改用 JOIN。我能够转换简单的查询，但我正在努力处理包含嵌套 SELECT 的更复杂的查询。例如:

update Records set ActiveStatus=0, TransactionStatus=0, LastUpdated=& 
where ActiveStatus!=5 and LastUpdated!=& 
and Pk in (select RecordPk from GroupRecordLink 
where GroupPk in (select Pk from Groups where ActiveStatus!=5 and 
DateTimeStamp>&))

我按照 this post 重写了它，但我不确定我的结果是否正确。

update Records r join (select distinct RecordPk from GroupRecordLink grl join 
Groups g on grl.Pk = g.Pk where g.ActiveStatus!=5 and g.DateTimeStamp>&) s
using (Pk) set ActiveStatus=0, TransactionStatus=0, LastUpdated=&  
where ActiveStatus!=5 and DateTimeStamp>&

谢谢

Answer 1

试试这个:

UPDATE Records 
INNER JOIN GroupRecordLink ON Records.Pk = GroupRecordLink.RecordPk 
INNER JOIN Groups ON GroupRecordLink.GroupPk = Groups.Pk AND Groups.ActiveStatus != 5 
SET Records.ActiveStatus = 0, 
    Records.TransactionStatus = 0, 
    Records.LastUpdated = & 
WHERE Records.ActiveStatus != 5 
  AND Records.LastUpdated != &;