SQL Server SQL Update statement with ID from other table, using relations [closed]

hc2pp10m 于 2023-04-10 发布在其他

关注(0)|答案(1)|浏览(153)

Closed. This question is opinion-based . It is not currently accepting answers.

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post .

Closed 4 days ago.
Improve this question

I need this at the moment:

update [dbo].[ProductionCostCenter] 
set signerId = 999 
where ProductionCostCenter.id = ReasonCode.ProductionCostCenterId 
  and ReasonCode.Id = 77777

My other way of doing this would be to get the ID from the first table, then using it in a second statement to update my value in question.

I am using SQL Server.

While writing this I thought of

update [dbo].[ProductionCostCenter] 
set signerId = 999 
where id in (select ProductionCostCenterId 
             from ReasonCode 
             where Id = 77777)

Is this the better way?

sql-server

来源：https://stackoverflow.com/questions/75940535/sql-update-statement-with-id-from-other-table-using-relations

1条答案

按热度按时间

7rtdyuoh1#

The second query, that uses in and the subquery, should work. I would still recommend exists here:

update ProductionCostCenter p
set signerId = 999
where exists (
    select 1 from ReasonCode r where r.id = 77777 and r.ProductionCostCenterId  = p.id
)

existsusually performs better than in , especially with the right index in place:

create index idxProductionCostCenter on ReasonCode(id, ProductionCostCenterId);

This assumes a 1-N relationship between the two tables. If you have a 1-1 relationship (which somehonw seems less likely), a join could feel more natural, as commented by Thom A:

update p
set signerId = 999
from ProductionCostCenter p
inner join ReasonCode r on r.ProductionCostCenterId  = p.id
where r.id = 77777

赞(0）回复(0）举报 2023-04-10

我来回答

SQL Server SQL Update statement with ID from other table, using relations [closed]

1条答案

相关问题

热门标签

最新问答