Updating column values of a table with column values from a different table

Question

I have a table (Table 1) with columns FN, LN, MN and eleven other columns. I have another table (Table 2) which has FN, LN, MN (only three columns).

I want to update my values FN, LN and MN in the Table 1 with the values FN, LN and MN from Table 2.

I do NOT need to Join on the basis of any other common column.

Please let me know if there is a way to do it.

I cannot use a SELECT * INTO statement because the structure of the two tables is not the same. Table 1 has 11 odd columns and Table 2 has 3 columns.

Can you provide a sample data of Table 1 and table 2 and your desired result. May be just 3 rows each. — Jithin Shaji
– Jithin Shaji, Commented May 28, 2014 at 3:52
Table 1 has : ID FirstName MiddleName LastName -----other columns--- Table 2 has : FirstName MiddleName LastName What I want is All the table2 values should be populate to Table1 with all the other column values of Table1 remaining the same. — user3681350
– user3681350, Commented May 28, 2014 at 5:13

Arseni Mourzenko · Accepted Answer · 2014-05-28 05:51:40Z

1

If Table 1 contains:

[FN]           [LN]     [MN]    [A]          [B]     [C]
--------------------------------------------------------
hello          world     123    something    else    456
other          row        45    demo         data    789
something      else      456    NULL         NULL    999

and Table 2 contains:

[FN]           [LN]     [MN]
----------------------------
table          two         1
just-a-demo    here        2
final          row         3

and the expected result is:

[FN]           [LN]     [MN]    [A]          [B]     [C]
--------------------------------------------------------
table          two         1    something    else    456
just-a-demo    here        2    demo         data    789
final          row         3    NULL         NULL    999

you can first get the expected result like this;

select [S].[FN], [S].[LN], [S].[MN], [F].[A], [F].[B], [F].[C] from
(
    select top 10
        row_number() over (order by (select 1)) as [Id], [A], [B], [C]
    from [Table 1]
) as [F]
left join
(
    select top 10
        row_number() over (order by (select 1)) as [Id], [FN], [LN], [MN]
    from [Table 2]
) as [S]
on [F].[Id] = [S].[Id]

Note:

The top 10: you may need to remove this, but you must be sure both tables return the same number of rows. If they don't, there is no way to do 1:1 mapping according to the information you gave in the comments.
row_number() over (order by (select 1)) simply puts "1" for the first returned row, "2" for the next one, etc. This is a weird way to join the results from two tables: I would expect to have an actual ID in both tables, or something which enables to do an actual join.

You can then insert it into a temporary table:

what you can do is to use the insert into with a subquery select like this:

insert into [TempTable] ([FN], [LN], [MN], [A], [B], [C])
    select [S].[FN], [S].[LN], [S].[MN], [F].[A], [F].[B], [F].[C] from
    (
        select top 10
            row_number() over (order by (select 1)) as [Id], [A], [B], [C]
        from [Table 1]
    ) as [F]
    left join
    (
        select top 10
            row_number() over (order by (select 1)) as [Id], [FN], [LN], [MN]
        from [Table 2]
    ) as [S]
    on [F].[Id] = [S].[Id]

and then replace [Table 1] by [TempTable].

edited May 28, 2014 at 5:51

answered May 28, 2014 at 3:18

Arseni Mourzenko

52.7k35 gold badges119 silver badges213 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3681350 Over a year ago

hey, the other column does not allow null values. When i try to execute the above it says "Cannot insert null into other columns", so I want the data in other columns to remain as it is. Any other way I can proceed?

Arseni Mourzenko Over a year ago

What do you mean by "to remain as it is"? If you're inserting rows, there is no prior data. If you're updating existent rows, then you should specify how the match between two tables should be done.

user3681350 Over a year ago

Basically I need to update data only the three columns with other columns values remaining the same. Let me know if you understood, otherwise i could explain in more detail.

Jithin Shaji · Accepted Answer · 2014-05-28 05:55:53Z

0

Is this the way that you are looking for.

Declare @table1 Table
    (id int identity(1,1), 
    FN varchar(15), 
    LN varchar(15), 
    MN varchar(15),
    Flag1 int default(1),
    Flag2 int default(1),
    Flag3 int default(0))

Declare @table2 Table
    (id int identity(1,1), FN varchar(15), LN varchar(15), MN varchar(15))

Insert into @table1 (Fn,LN,MN) Values 
('A','B','C'),('A','B','D'),('A','X','C')
Insert into @table2 (Fn,LN,MN) Values 
('A','B','C'),('A','B','D'),('A','Y','C')

enter image description here

Merge into @table1 A
using @table2 B On 
        A.FN = B.FN AND
        A.LN = B.LN AND
        A.MN = B.MN
When Not Matched then
Insert (FN,LN,MN)
Values (B.FN,B.LN,B.MN); 

Select * from @table1

Result

enter image description here

answered May 28, 2014 at 5:55

Jithin Shaji

6,1015 gold badges27 silver badges47 bronze badges

Collectives™ on Stack Overflow

Updating column values of a table with column values from a different table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related