update

Question

Post reply

update

Sarwan

SSCommitted

Points: 1999
More actions
November 21, 2008 at 8:57 pm

#92832

can we update table A column using table B column .
Please can anyone explain with example

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

This is actually simple, and I'm pretty sure you could find an example in Books Online. Why don't you check it out.

Sarwan SSCommitted Points: 1999 More actions · Answer 2

is that neccessary to have joining column from both the tables

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I don't know. You haven't shown me what you are talking about.

You might also want to read the first article linked in my signature block below. It will give you excellent advice on how best to ask questions that will get you better answers.

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 4

Heres an example of what you can do. Note that you DO need a common ID to join the two table...

Firstly some example tables. We have two tables with names - but the first table is missing the last name. We will use #names2 to populate the lname into @names.

CREATE TABLE #names(

[num] [int] IDENTITY(1,1) NOT NULL,

[fname] [varchar](25) NULL,

[lname] [varchar](25) NULL

)

insert #names(fname,lname)

select 'Bart' , 'x'

union all select 'Bob' , 'x'

union all select 'Carl' , 'x'

union all select 'Hommer' , 'x'

union all select 'Lenny' , 'x'

union all select 'Tom' , 'x'

CREATE TABLE #names2(

[num] [int] IDENTITY(1,1) NOT NULL,

[fname] [varchar](25) NULL,

[lname] [varchar](25) NULL

)

insert #names2(fname,lname)

select 'Bart' , 'Simpson'

union all select 'Bob' , 'Jones'

union all select 'Carl' , 'Bell'

union all select 'Hommer' , 'Simpson'

union all select 'Lenny' , 'Thomson'

union all select 'Tom' , 'Smith'

It is usually best to try to write a select query that shows the original values and the new values. This allows a proper verification of the data.

select #names.*, #names2.lname

from #names inner join #names2 on #names.num = #names2.num

Finally, change the select to an update query. Keep the FROM clause the same

UPDATE #names set #names.lname = #names2.lname

from #names inner join #names2 on #names.num = #names2.num

I hope that example helps.

B