Update one field to another in another table

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)

Update one field to another in another table

Author

Topic

dolphinlover
Starting Member

2 Posts

Posted - 2005-11-18 : 20:36:13

I need to be able to select a unit price field from one table based on the most recent order date and populate that price into another tables field standard unit cost. These two tables both share a common field part_id.

Is that possible?

Kristen
Test

22859 Posts

Posted - 2005-11-18 : 21:15:10

Something like this?


UPDATE U
SET MyCost = UnitPrice
FROM MyTargetTable AS U
JOIN MyPurchaseTable AS P
     ON P.part_id = U.part_id
JOIN
(
	SELECT	part_id,
		[MAX_OrderDate] = MAX(OrderDate)
	FROM	MyPurchaseTable
	GROUP BY part_id
) AS P2
	 ON P2.part_id = P.part_id
	AND P2.[MAX_OrderDate] = P.OrderDate

If you could get multiple records in MyPurchaseTable for the same part_id on the same date but with different UnitPrice this will pick a random UnitPrice, so the update might be enhanced reworked for the MAX(UnitPrice) on the most recent OrderDate

Kristen

dolphinlover
Starting Member

2 Posts

Posted - 2005-11-21 : 17:32:00

I think I goofed somewhere, there is actually another table involved.

ICFPM –FIELDS (std_mtl_cst_add) this is the field I need populated with the most recent unit price from pofod.unit_price)
POFOM—FIELDS (order_date) this determines the most recent date (this table links to POFOD with same field po_id)
POFOD—FIELDS (part_id, unit_price) this is the table I need to get the unit price from.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources