Learn how to a build a cloud-first strategyRegister Now


Update all rows

Posted on 2011-05-09
Medium Priority
Last Modified: 2012-05-11
I want to update all rows into one sql update statement.

update ChurchTbl set
CountryID = 2
where ChurchName = 'Åland';

2      Åland
3      Albania
4      Algeria
5      American Samoa
6      Andorra
7      Angola
8      Anguilla
9      Antarctica
10      Antigua and Barbuda
11      Argentina
12      Armenia
13      Aruba
14      Ascension Island
15      Australia
16      Austria
17      Azerbaijan
18      Bahamas
19      Bahrain
20      Bangladesh
21      Barbados
22      Belarus
23      Belgium
24      Belize
25      Benin
26      Bermuda
27      Bhutan
28      Bolivia
29      Bosnia
30      Bosnia and Herzegovina
31      Botswana
32      Bouvet Island
33      Brazil
Question by:lulu50
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35721322

Please explain, as your question is not clear.  That SQL statement looks OK on its own, and your sample data seems to indicate that you already have the result you wanted.


Author Comment

ID: 35721404
I want to have a sql statement that would implement the data
the data that you see is in the excel sheet not in the database
2      Åland
3      Albania
4      Algeria
5      American Samoa
6      Andorra
7      Angola
8      Anguilla
9      Antarctica

the SQL statement is to update one record but I want it to update all these records at once
LVL 77

Expert Comment

ID: 35721427
If you truly mean ALL rows then remove the condition.

update ChurchTbl set
CountryID = 2;

Backup before testing.

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 81

Expert Comment

ID: 35721498
Are you trying to update all records based on the value in a specific column of each record??

LVL 52

Accepted Solution

_agx_ earned 2000 total points
ID: 35721949
the data that you see is in the excel sheet not in the database  

If you *really* don't have a "Country" table in your db (which is odd) you could insert it into a table variable. Then use a JOIN to UPDATE your main table.  But normally it makes more sense to have a permanent Country table.

Assumes SQL 2005

declare @country table (
countryId int, 
countryName nvarchar(100)

insert into @country (countryId, countryName)
select 2,N'Åland' union all
select 3,N'Albania' union all
select 4,N'Algeria' union all
select 5,N'American Samoa' union all
select 6,N'Andorra' union all
select 7,N'Angola' union all
select 8,N'Anguilla' union all
select 9,N'Antarctica' union all
select 10,N'Antigua and Barbuda' union all
select 11,N'Argentina' union all
select 12,N'Armenia' union all
select 13,N'Aruba' union all
select 14,N'Ascension Island' union all
select 15,N'Australia' union all
select 16,N'Austria' union all
select 17,N'Azerbaijan' union all
select 18,N'Bahamas' union all
select 19,N'Bahrain' union all
select 20,N'Bangladesh' union all
select 21,N'Barbados' union all
select 22,N'Belarus' union all
select 23,N'Belgium' union all
select 24,N'Belize' union all
select 25,N'Benin' union all
select 26,N'Bermuda' union all
select 27,N'Bhutan' union all
select 28,N'Bolivia' union all
select 29,N'Bosnia' union all
select 30,N'Bosnia and Herzegovina' union all
select 31,N'Botswana' union all
select 32,N'Bouvet Island' union all
select 33,N'Brazil' ;

--- do update ...
update ct 
set ct.CountryID = tmp.CountryId
from  ChurchTbl  ct inner join @country c ON c.CountryName = ct.ChurchName 

Open in new window


Author Closing Comment

ID: 35722231
Thank you

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 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