Solved

Update multiple records at once

Posted on 2008-06-23
18
718 Views
Last Modified: 2009-01-10
Hello,

I need to update multiple records at once. For example:

UPDATE T1 SET Balance = 10 WHERE AccID = 5454;
UPDATE T1 SET Balance = 15 WHERE AccID = 5455;
(...)

I execute many (like 300+) of those updates at once. What I did is I put all of them into one command. But that's taking so long to execute. Is there any way to do it faster? I've seen it executes a lot of reads and some writes, so I think it spends most of the time finding each "AccID".

Any ideas?

Thank you!
0
Comment
Question by:starusa
  • 6
  • 4
  • 4
  • +1
18 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21848565
update t1
set  balance = case accid when 5454 then 10 when 5455 then 15 end
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21848570
it's possible:
UPDATE T1 

  SET Balance = CASE AccID 

    WHEN 5454 THEN 10

    WHEN 5455 THEN 15

   END

WHERE AccID IN (5454 , 5455);

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21848583
chap, you forgot the WHERE clause...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21848590
of course, you shall have a index on AccID column...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21848608
I was under the assumption that the user wanted to update a lot more than 2 records...was just showing the structure they could use to update everything at one time.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21848627
yes, but 300+ records are most eventually not ALL the records...
without the where clause, all the "other" records will be updated to the value specified in the ELSE of the CASE statement, eventually to NULL if no ELSE clause is given.
no good idea!
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21848689
I think if I had 300 or so to do, I'd do the following..

run the following script to create a new table with columns for the AccId and newBalance

SELECT DISTINCT AccID,Cast(0 as float) AS newBalance INTO updateBalances FROM T1

now, just fill in the newBalances for each customer by their AccId value.

Once you have that, run the following script to update their original balances

UPDATE T1 SET Balance = updateBalances.newBalance FROM T1 INNER JOIN updateBalances ON T1.AccId = updateBalances.AccId
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21848965
you're right...I did forget to add the else statement:

update t1
set  balance = case accid when 5454 then 10 when 5455 then 15 else balance end
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21849004
I agree with CMYScott...that is really the best option for the update.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:starusa
ID: 21849400
Hey,
actually I'm updating 2300+ records, I gave you guys the wrong number(300+). Turned out the query is huge and I got an error:

Microsoft SQL Native Client error '80040e14'

The query processor ran out of stack space during query optimization. Please simplify the query.

Guess I'm gonna try the second option, the one CMYScott suggested.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21849427
Is there any type of pattern to what you're updating the values to?  Do you happen to have the list in a spreadsheet somewhere that you can import into SQL and use?  I'd have to see you go in and update 2300 records w/ the new values...you know what I mean?
0
 

Author Comment

by:starusa
ID: 21849471
Well, this is for an accounting application. It allows the user to correct an amount in the past. Let's say one month ago. When that happens, the system has to recalculate the balance from that point until today.  So, a lot of updates are executed. Maybe there's an easier way, you guys tell me.

thanks!
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21849486
I agree with chap - wherever you have those values, you need to pull them into Excel, Access - SOMETHING that you can then easily get them into SQL.  Not only is doing them manually a major HASSLE - its also a major risk of mistakes.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21849496
We really need more info...how are the values stored?  Monthly only or daily?  If someone updates a balance for Jan 08 to 100 from 110, what should the balance be today?  Is interest taken into account?
0
 

Author Comment

by:starusa
ID: 21850516
Values are already in the SQL database. For each debit or credit recorded a row is created. Basically I have the Amount column, which is the debit or credit, then the Balance column. There are many records for each day, which represent a deposit, a withdraw, a bill paid, a internal operation, transfer etc. So, let's say someone transfer $10 from this account. A debit of $10 is inserted on the amount, and the balance descreases $10. Now, suppose the $10 is inserted today, but with the date of 05/23, or one month ago. Then, since there are many records after such date, the balance must be recalculated for each row. That's what generates all those UPDATES. The scenario is not so simple, because the application allows the user to update many records without recalculating the balance. So I have to check each row to really make sure the amount is correctly added or subtracted from the balance. I plan to change that on the future. I didn't write the system. So, the first step I'm taking is migrating it from Access to SQL 2005. Then I would rewrite it using ASP.NET.

Here's a sample:

DateOper                            Amount         Balance
2007-01-01 00:00:00.000        0.00      78787.00
2007-01-02 00:00:00.000        190.00      78977.00
2007-01-02 00:00:00.000        10.00      78987.00
2007-01-02 00:00:00.000        50.00      79037.00
2007-01-02 00:00:00.000        10.00      79047.00

Well, hope this helps.

Thank you!
0
 
LVL 11

Expert Comment

by:CMYScott
ID: 21850673
given the structure of your data - assuming you don't have the complete history (in your example, you don't have an initial deposit which creates the balance - nor does it appear that you have a unique index which indicates the exact order - as your dates only show dates and can duplicate on the same day)...

1. I would recommend you start by creating a new table from your existing data and adding an IDENTITY field..

2.  Then, I would look into creating a Stored Procedure using a CURSOR to update the balances (the cursor could easily be removed if you have ALL the history)

0
 

Author Comment

by:starusa
ID: 21850720
Actually that's only a fragment of the table. It does have an Identity field with a primary key. Those accounts are years old, so they do have a history, If I understand what you're saying. They started from 0 and had a initial deposit.
0
 
LVL 11

Accepted Solution

by:
CMYScott earned 500 total points
ID: 21850819
then you should be able to do an update query - at any time - to update all the balances..

the code below is based on a simple table I made up..

idx - is the primary key (IDENTITY)
ItemDate - is the transaction date column
ItemAmount - is the amount of the individual transaction
Balance - is the balance after a transaction has been applied




UPDATE items set balance = ItemAmount + 

  ISNULL(

    (SELECT SUM(ItemAmount) 

    FROM items si 

    WHERE (si.ItemDate< i.ItemDate 

    OR	  (si.ItemDate=i.ItemDate AND si.idx < i.idx))

    AND	  si.AcctNum = i.AcctNum),0)

    FROM	items i

Open in new window

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now