Update Statement

[ShowersDK]

I need help with an update statement.

UPDATE mrgrpdm
SET quantity_required = stfcedits.quantity_required
FROM mrgrpdm a, stfcedits b
WHERE b.product = a.product and
b.forecast_reference = a.forecast_reference

the stfcedits table has an entry_date column and there can be multiple
entry_dates per product and forecast_reference combination. I want to be able to only update with the max(entry_date) row for that combination. Can anyone suggest the syntax. I am using SQL Server 7.0.

Thanks,
Darren

 

[bperry]

You could consider this (untested):

[tt]
UPDATE mrgrpdm
SET quantity_required =
(Select TOP 1 quantity_required
From stfcedits
Where stfcedits.product = mrgrpdm.product
and stfcedits.forecast_reference =
mrgrpdm.forecast_reference
Order By entry_date DESC)
[/tt]

Test before using on live data.

 

[AWithers]

add in an extra line

and entry_date in(select max(entry_date) from stfcedits group by product,forecast_reference )

Hope this helps

Andy

 

[ShowersDK]

Not familiar with "TOP". Looked in transact-sql help and couldnt locate it.

Thanks,
Darren

 

[bperry]

TOP (as you can see) is a sub-clause within SELECT.

You will find it if you search the index tab in BooksOnLine.

rgrds, etc
bperry