如何使用join进行删除更新复杂查询？ [英] How to make delete update complex queries using join ?

问题描述

以下是我使用子查询创建的脚本

Hi ,

below are my scripts which i have created using subqueries

UPDATE Report
SET ReporttypeId = (SELECT ReportTypeId
FROM ReportType AS rt WITH (NOLOCK)
WHERE code = 'SYSTEM' AND Name= 'System')
WHERE ReportTypeId = (SELECT ReportTypeId
FROM ReportType AS rt WITH (NOLOCK)
WHERE code = 'ADMIN' AND Name= 'Admin')

GO

Delete from ReportType
Where ReportTypeId =
(SELECT ReportTypeId
FROM ReportType AS rt WITH (NOLOCK)
WHERE code = 'ADMIN' AND Name= 'Admin')

有没有b etter方法使用连接实现相同的方法??

Is there any better way to achive the same using joins ??

推荐答案

这是一个很好的查询。我没有看到它的任何错误，除了你可能想要考虑重构表提示NO LOCK。我不是NO LOCK的粉丝，因为它可以导致脏读，它不仅可以错过记录，而且可以在通过不同的进程更新之前和之后读取。

That is a good query. I don't see any wrong with it, except you might want to consider refactoring the table hint NO LOCK from it. I am not a fan of NO LOCK as it can cause dirty reads and it can not only miss records but read one before and after it has been updated by a different process.

这看起来像一次性维护问题，因此不需要优化。你可以想象并创建一个表var来保存from / to值，但这几乎不值得。

This looks like a 1 off maintenance problem so there should be no need for optimisation. You could get fancy and create a table var to hold the from/to values but it is hardly worth it.

这篇关于如何使用join进行删除更新复杂查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！