October 26th, 2009, 08:20 AM
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 8
Time spent in forums: 2 h 1 m 24 sec
Reputation Power: 0
|
|
|
MSSQL update one table with data from another
Hi,
i have two tables:
Products and Temp Products
they both have the following structure
supp_id
manf_part_no
manufacturer
description
cost_price
rrp
avail_qty
date_added
i need to update the data in Products with the data from TempProducts where supp_id is the same in both tables and the manf_part_no is the same in both tables.
we need to remember though that supplier 1 may have manf_part_no 200 and supplier 2 may also have that!
i currently have this query but it seems to only update supp_id1 and updates all products with the same price quantity etc.
Code:
UPDATE p1
SET p1.avail_qty = t1.avail_qty, p1.cost_price = t1.cost_price, p1.rrp = t1.rrp, p1.date_added = t1.date_added, p1.description = t1.description
FROM dbo.products AS p1 INNER JOIN
dbo.TempProduct AS t1 ON p1.manf_part_no = t1.manf_part_no AND p1.supp_id = t1.supp_id CROSS JOIN
p1
|
Codewalkers Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
