Help with a update statment using inner join

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

SQL Server 2000 Forums

Transact-SQL (2000)

Help with a update statment using inner join

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-12-27 : 16:28:37

Hi All,

I need help with a simple update statement using inner join.

Lets say I have a table called Test with following fields:
ProductID(varchar type), Name(varchar type)

I have another Table called Test2 with following fields:
ProductID(varchar type), NewProductID(nvarchar type), Name(varchar type)

Now I want to update all the ProductID in Test1 table with the NewProductID from Test2 table, where both table ProductID are similar.

Let say in Test1 I have the following records.
ProductID,Name
123,prod1
456,prod2
789,prod3

Let say in Test2 I have the following records.
ProductID,NewProductID,Name
123,123NN,prod1
456,456UU,prod2
789,789TT,prod3

I want to update the Test1 records as follows:
ProductID,Name
123NN,prod1
456UU,prod2
789TT,prod3

Since i have thousands of records, I want to create an update statement using inner join.

How can I achieve this. Please let me know.

Thanks a million in advance.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-12-27 : 16:51:35

I know the soultion.

update test1
set test1.productid=test2.newproductid
from test1 join test2
on test1.productid=test2.productid

BrianArmstrong
Starting Member

6 Posts

Posted - 2006-12-27 : 16:54:01

Is this what you want to do?

UPDATE Test1
SET ProductID = T2.NewProductID
FROM Test1 T1 INNER JOIN Test2 T2 
ON T1.ProductID = T2.ProductID

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2006-12-27 : 17:13:37

Yes. Its the samething...isn't it?
The only difference is, that you used alias. Right?

But thanks for your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-01 : 03:49:06

quote:
Originally posted by zeeshan13

Yes. Its the samething...isn't it?
The only difference is, that you used alias. Right?

But thanks for your help.

Yes. Alias can be useful if the table name is long

Also if you use table variable, you cant directly refer that as you specified and alias is the way to go

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources