Update all rows

Posted on 2011-05-09
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 92

    Expert Comment

    by:Patrick Matthews

    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

    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

    If you truly mean ALL rows then remove the condition.

    update ChurchTbl set
    CountryID = 2;

    Backup before testing.

    LVL 76

    Expert Comment

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

    LVL 51

    Accepted Solution

    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

    Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 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

    11 Experts available now in Live!

    Get 1:1 Help Now