• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Update all rows

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
1 Solution
Patrick MatthewsCommented:

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.

lulu50Author Commented:
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
If you truly mean ALL rows then remove the condition.

update ChurchTbl set
CountryID = 2;

Backup before testing.

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

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

lulu50Author Commented:
Thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now