Update multiple records at once

update t1
set  balance = case accid when 5454 then 10 when 5455 then 15 end

it's possible:

UPDATE T1 
  SET Balance = CASE AccID 
    WHEN 5454 THEN 10
    WHEN 5455 THEN 15
   END
WHERE AccID IN (5454 , 5455);

Select allOpen in new window

chap, you forgot the WHERE clause...

of course, you shall have a index on AccID column...

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.

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!

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

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

I agree with CMYScott...that is really the best option for the update.

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.

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?

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!

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.

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?

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!

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)

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.