Solved

Multiple Updates in TSQL ?

Posted on 2012-03-26
6
286 Views
Last Modified: 2012-08-14
Hi, below is my sample data from my table

Code          oldrate  

AZ/09          55.4      
Ac/07          46.4      

I have around 74 records in my table and I want to update the oldrate column
with new rates given to me for all 74 different codes.Please note that the
new rates are different to all codes.

Can any one please let me know if there is a better way of updating all these
instead of using UPDATE multiple types for all these codes in TSQL?

Many Thanks
0
Comment
Question by:gvamsimba
  • 3
  • 2
6 Comments
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 37765203
use the following sample, this should help you;

UPDATE tableName
SET newRate =
CASE CODE
      WHEN 'AZ/09' THEN '50.00'
      WHEN 'Ac/07' THEN '45.50'
      ...................
      ...................
END
0
 

Author Comment

by:gvamsimba
ID: 37765256
Hi, but I dont have the column called 'new rate'. I have just  have to update
the column 'oldrate' with new rates.

Thanks
0
 
LVL 7

Assisted Solution

by:waltersnowslinarnold
waltersnowslinarnold earned 200 total points
ID: 37765260
Oh okay, Just replace the newRate as oldRate like;

UPDATE tableName
SET oldrate  =
CASE CODE
      WHEN 'AZ/09' THEN '50.00'
      WHEN 'Ac/07' THEN '45.50'
      ...................
      ...................
END
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Comment

by:gvamsimba
ID: 37765297
Hi  waltersnowslinarnold, but I am getting the below error message when I do that..

Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'oldrate',
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 37765313
Is oldRate column defined with NOT NULL Constraint, if so have a ELSE clause to 0.00 as below;

UPDATE tableName
SET oldrate  =
CASE CODE
      WHEN 'AZ/09' THEN '50.00'
      WHEN 'Ac/07' THEN '45.50'
      ...................
      ...................
      ELSE 0.00
END
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 300 total points
ID: 37765875
Actually, you don't want to use ELSE 0.00

You will want to use the oldrate.

But if you do it this way, you will update your entire table and if you have a lot of rows, say 10000, and you want to only change 10, that is a lot of changes that you really don't need to make.

You may want to think about using a temporary table and put the values with the keys and rate in it and then joining it with the original table.

CREATE TABLE #temptable (Code varchar(10) NOT NULL PRIMARY KEY, NewRate money)
INSERT INTO #temptable
VALUES ('AZ/09', 50.00),
('Ac/07', 45.50)
....

UPDATE tablename
SET OldRate = B.NewRate
FROM tableName
INNER JOIN #temptable B ON tableName.Code = B.Code

This will only update those that match from the temptable instead of all the rows in the entire table.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 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