Solved

Multiple Updates in TSQL ?

Posted on 2012-03-26
6
288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 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