如何更新sql server中的多行 [英] How to update multiple rows in sql server

问题描述

您好b $ b

我有一个表CustomerInfo，其中我选择了10条记录，我想同时更新这10条记录，我选择的是



例子

Hi
I have one table CustomerInfo in which I am selecting To 10 records and I want to simultaneously update those 10 records which is I am selecting

Example

SELECT distinct top(10) * from [dbo].[CustomerInfo] where [RefundStatus]='Dispute' or [SynJobTimestamp]=null
    or  [ProcessOwnerID]=null order by RefundID asc

我要更新这些记录



请帮忙急

I want to update these records

Please help its urgent

推荐答案

SELECT distinct top(10) * INTO #MyTable from [dbo].[CustomerInfo] where [RefundStatus]='Dispute' or [SynJobTimestamp]=null
    or  [ProcessOwnerID]=null order by RefundID asc
    
    Update CI
    SET CI.[column] = value
    FROM  dbo.[CustomerInfo] CI 
    INNER JOIN #MyTable TI
    ON CI.customerID = TI.CustopmerID

改编自：

Adapt from this:

update table1 set field1 = 'somevalue'
where field2 in (select distinct top 10 field2 from table1)

这是哪个主键？我假设RefundID为主键。

Which is the Primary key here? I assume "RefundID" as primary key .

update CI
set CI.[Description]='Dispute'
from [CustomerInfo] CI
inner join
(SELECT distinct top(10) * from [dbo].[CustomerInfo]
where [RefundStatus]='Dispute'
or [SynJobTimestamp]=null
or  [ProcessOwnerID]=null
order by RefundID asc) [tmpCI]
on CI.RefundID=tmpCI.RefundID

建议不要在子查询中使用order by。所以下面的查询给出了最好的结果，

It is advisable not to use order by in sub query . so the below query give best result ,

update CI
set CI.[Description]='Dispute'
from [CustomerInfo] CI
inner join
(SELECT distinct top(10) * from [dbo].[CustomerInfo]
where [RefundStatus]='Dispute'
or [SynJobTimestamp]=null
or  [ProcessOwnerID]=null) [tmpCI]
on CI.RefundID=tmpCI.RefundID

如果您有复合键或任何其他键，请相应地更改条件。

If you have composite key ,or any other key ,change the condition accordingly .

这篇关于如何更新sql server中的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！