Complex SQL statement?

Question

Hi, 

I hope this is the right forum for this question.  I am not sure what is meant by "data mining"...

I would really appreciate some help figuring out an SQL statement to achieve the following...

I have two tables:


     TABLE A                 TABLE B
column1   column2     column1
  1            1 ABC              1
  2            2 NBC              3
  3            3 CBS              2


I want to replace the data in Table B's column1 with the data in Table A's column2 where Table B's column1 = Table A's column1

thanx for any help.

Answer 1

Keneo,

Try

 

Declare @tab1 table(col1 int, col2 varchar(20))
Declare @tab2 table(col1 varchar(20))
insert into @tab1 values(1,'1 ABC'),(2,'2 NBC'),(3,'3 CBS')
insert into @tab2 values(1),(3),(2)

select * from @tab1
select * from @tab2

Update @tab2 set t2.col1=t1.col2 from @tab2 t2 inner join @tab1 t1 on t1.col1=t2.col1

select * from @tab1
select * from @tab2

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Then you should have chosen a more appropriate forum like Transact-SQL.

Answer 3

I just realized that I left out something very important.

Table A is in a different database than table B.

Answer 4

Simply use a 3-part identifier:

SELECT  *
FROM    DB1.dbo.Table1 T1
        INNER JOIN DB2.dbo.Table2 T2 ON T1.ID = T2.Table1ID ;

See also Using Identifiers.