Solved

Multiple Updates in TSQL ?

Posted on 2012-03-26
6
283 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I get blanks instead of zero's to show up in my results? 2 30
Upgrading Integration Services 3 28
all records from previous month 6 45
tempdb latch contention 12 50
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

864 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