更新引用另一个表 [英] Update referencing another table

问题描述

我有一条语句需要写(用通用名称表示，因为这是为了工作)，以更新表'tUpd'中的列'updCol'.tUpd还具有一个列"linkCol"，该列存在于另一个表tOther中.tOther还有另一列"idCol".

I have a statement that needs writing (with generic names for stuff, since this is for work) to update a column 'updCol' in table 'tUpd'. tUpd also has a column 'linkCol' which is present in another table tOther. tOther has another column 'idCol'.

我的问题是更新tUpd中行的updCol值，这些值通过linkCol对应于具有给定idCol值的行.

My problem is to update the updCol value of rows in tUpd which correspond via linkCol to rows with a given idCol value.

以下是我认为应该可行的解决方案；

One solution I think should work is the following;

update
    tUpd
set
    updCol = XXX
where exists (
    select
        idCol
    from
        tOther
    where
        tOther.linkCol = tUpd.linkCol
    and tOther.idCol = MY_ID
)

但是，我担心这种方法会导致性能下降，因为以前我曾被警告过与性能相关的子查询-该子查询将对tUpd的每一行运行一次，这是正确的吗??

However, I have worries that this approach will lead to poor performance, since I've been warned of sub-queries in relation to performance before - this sub-query will be run once for each row of tUpd, is this correct?

有人有更好的建议吗?

重要更新:我的工作场所不惜一切代价避免使用SQL JOIN，而是喜欢使用例如 where a.col = b.col 的where子句进行联接.可以说这很尴尬，但是允许灵活地进行日志记录，而我对此并不完全了解.所以，我正在寻找非JOIN使用的解决方案:)

Important Update: my workplace avoids using SQL JOINs at all costs, preferring to join within the where clauses using, eg, where a.col = b.col. This is arguably rather awkward but allows a flexibility in especially logging which I don't fully understand. SO, I'm looking for non-JOIN-using solutions :)

推荐答案

就像这样

UPDATE DestinationTable
SET DestinationTable.UpdateColumn =  SourceTable.UpdateColumn 
FROM SourceTable
WHERE DestinationTable.JoinColumn = SourceTable.JoinColumn

这篇关于更新引用另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！