CODEWITHSUNDEEP
sql-server-2008sql-updatesql
Aivan Monceller
Aivan Monceller

Reputation: 4670

Update Column based on SELECT with Parameter coming from UPDATE

I want to do something like this.

UPDATE tbl_states AS ts
SET    tbl_states.country_id = (SELECT tbl_countries.country_id
                                FROM   tbl_states ts1
                                       JOIN tbl_countries
                                         ON tbl_countries.country_id_id =
                                            ts1.country_id
                                WHERE  ts1.country_id_id = ts.country_id_id)

I want to update old country_id which came from different database to new country_id based on new primary keys inserted on the new database. To give you an idea here is the schema.

CREATE TABLE [dbo].[tbl_countries](
    [country_id] [int] IDENTITY(1,1) NOT NULL,
    [country_id_id] [int] NULL,
    [country_name] [varchar](50) NULL)

country_id_id is the old country_id referenced on the next table I will show you tbl_states

CREATE TABLE [dbo].[tbl_states](
    [state_id] [int] IDENTITY(1,1) NOT NULL,
    [state_name] [varchar](50) NULL,
    [country_id] [int] NULL,
    [state_abbr] [char](3) NOT NULL)

I want to update country_id column of this table tbl_states to primary column of the table above using the following select statement to get the primary key.

SELECT tbl_countries.country_id
FROM   tbl_states_old
       JOIN tbl_countries
         ON tbl_countries.country_id_id = tbl_states_old.country_id

Sorry for the title, I don't know what this is called. Could you help me with this?

Upvotes: 2

Views: 1498

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

UPDATE s
    SET country_id = c.country_id
    FROM tbl_states s
        INNER JOIN tbl_countries c
            ON s.country_id = c.country_id_id

Upvotes: 3

Related Questions