Solved

Multiple Updates in TSQL ?

Posted on 2012-03-26
6
284 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Conversion of IIF statement into CASE - Syntax issue 17 37
Get Duration of last Status Update 4 34
Sql query 107 56
Sql Query 4 30
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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

816 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

8 Experts available now in Live!

Get 1:1 Help Now