June 10, 2013 at 12:51 pm
I have a temporary table in SQL Server 2008. I want to perform UPDATE row-by-row.
As of now I am using a less efficient way by adding an Id column and then updating the row number. Later, I am running a loop and picking row with Id matching the loop counter.
Is there any more efficient way to iterate the table row by row and performing Update operation?
I have MERGE in mind, but the problem is that I want to check several conditions before update is performed. With MERGE, I will be bound to use CASE WHEN.
There are two tables, #Temp1 and SalesRecord. I want to read from #Temp1 and update SalesRecord
Both tables have few similar columns.
#Temp1 (CustId int, AllocVal decimal, RowId)
SalesRecord (CustId int , Points decimal)
declare @Total as int
select @Total = count(*) from #Temp1
declare @counter as int
set @counter = 1
declare @CurrentVal as decimal
declare @CurrentCustomer as int
declare @SumPoints as decimal
while (@counter <= @Total)
begin -- Get row from #Temp1
select @CurrentCustomer = CustId, @CurrentVal = allocVal from #Temp1 where RowId = @counter
select @SumPoints = Sum(Points) from SalesRecord where CustId = @CurrentCustomer
if ((@CurrentVal + @SumPoints) <= 1)
begin
update SalesRecord set Points = @CurrentVal
end
else
begin
delete from SalesRecord where CustId = @CurrentCustomer
end
set @counter = @counter + 1
end
"Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "
Richard Bach
June 10, 2013 at 12:59 pm
MERGE is exactly what I would use here. I am not sure I understand your reservations about using MERGE for this.
If you want specific coding help we need you to post a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2013 at 1:06 pm
Also, you need to define more the logic you're using because you haev some possible flaws in your code and I see no reason to go row by (agonizing) row.
A set based operation will be more effective for you.
June 10, 2013 at 1:06 pm
With MERGE I want to know whether I can do something like this:
MERGE <table name> USING <sql>
WHEN MATCHED THEN
IF (condition) Then (Update Query)
When NOT Matched Then
If (Condition) Then (Multiple SQL Queries)
"Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "
Richard Bach
June 10, 2013 at 1:16 pm
@RDBMS (6/10/2013)
With MERGE I want to know whether I can do something like this:MERGE <table name> USING <sql>
WHEN MATCHED THEN
IF (condition) Then (Update Query)
When NOT Matched Then
If (Condition) Then (Multiple SQL Queries)
MERGE is a single statement, it is not used to control flow of logic. Here you have mixed logic flow and a single statement into one.
Aside from the multiple queries you can achieve most of what you are looking for.
When MATCHED AND (condition)
Update..
When NOT MATCHED AND (condition)
Delete...
If you truly need to do this type of more complicated logic you will need to break this into a couple logical blocks first. With no more details it is hard to give much of an example of what I mean here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply