Click here to Skip to main content
15,300,827 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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

SQL
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
Posted
Comments
Thava Rajan 12-Jun-14 9:34am
   
is refundid primary key of cutomerinfo table?

SQL
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:
SQL
update table1 set field1 = 'somevalue'
where field2 in (select distinct top 10 field2 from table1)
   
v2
Which is the Primary key here? I assume "RefundID" as primary key .

SQL
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


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

SQL
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
Update CustomerInfo
Set 
Col1 =  ci.Col1 + 'Updated'
Col2 =  ci.Col2 + 'Updated'
Col3 =  ci.Col3 + 'Updated'
SELECT TOP 10 * FROM CustomerInfo ci
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900