Easy update question betwee two tables

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

SQL Server Development (2000)

Easy update question betwee two tables

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2003-03-26 : 21:02:29

Not sure why but I am having a hard time with this one.

I have two tables.

Products_temp
name nvarchar 50
List_Price int 4

Products_e
name nvarchar 50
List_Price int 4

Both tables have the name of the product in them.
Products_temp has the list price while Products e does not.

How would I write an update statement to get them over??

Thanks in advance,
Chris

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-26 : 21:26:02

Hi

Does this help ? http://www.sqlteam.com/item.asp?itemid=3876

Damian

Edited by - merkin on 03/26/2003 21:26:36

chrispy
Posting Yak Master

107 Posts

Posted - 2003-03-26 : 21:39:18

Merkin,
Link doed not work but I was able to find the article. Excellent article.

Problem is that I was not correct in assuming what I needed.

Here is what I have (correct this time)

Products_temp
name nvarchar 50
List_Price int 4

Products_e
productid int 4
List_Price int 4

Products
UID int 4
name nvarchar 50

Now let me try to explain again :)
Products_e and Products are related by the UID and productID

Products has the name and Products_temp has the name and the list price. I am trying to get the list price update into the Products_e.

Makes it even more difficult. (for me at least).

Thanks again,
chris

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-03-26 : 21:47:50

Hi

Same technique as that article, just needed a little expanding to fit your tables.



--Assuming that UID is productid, I'd change that...it's dumb 

UPDATE Products_e
Set List_Price = Products_temp.List_Price
FROM
Products_e
INNER JOIN Products ON Products.uid = Products_e.Productid
INNER JOIN Products_Temp ON Products_Temp.name = Products.Name