complex SQL statement? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

complex SQL statement?

Author

Topic

keneo
Starting Member

3 Posts

Posted - 2012-04-05 : 02:00:04

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

I have two tables:

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

.....TABLE B
column1
....1
....3
....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

Gonzaclaves
Starting Member

2 Posts

Posted - 2012-04-05 : 04:28:25

I'm not a guru but I believe this should solve your problem:

update Table_B
set Table_B.Column1 = (select Table_A.Column2 from Table_A where Table_B.Column1 = Table_A.Column1)

Gonzaclaves :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-05 : 04:33:17

update b
set column1 = a.column2
from table_b as b
join table_a as a on a.column1 = b.column1

Be clear this update is working only one time. After that you can't match again via a.column1 = b.column1.

No, you're never too old to Yak'n'Roll if you're too young to die.

keneo
Starting Member

3 Posts

Posted - 2012-04-05 : 10:19:57

Thank you for your help.
those solutions look promising.
but there is one more important complication that I havent mentioned.

Table A is in a different database than table B.
I am using MS SQL server 2005.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-05 : 10:23:51

quote:
Originally posted by keneo

Thank you for your help.
those solutions look promising.
but there is one more important complication that I havent mentioned.

Table A is in a different database than table B.
I am using MS SQL server 2005.

update b
set column1 = a.column2
from databaseB.dbo.table_b as b
join databaseA.dbo.table_a as a on a.column1 = b.column1

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources