(ch************ *@gmail.com) writes:
I was hoping someone could help me with what I'm sure is a very simple
problem...I just can't seem to find the syntax!
I'm wanting to update the rows in 'tbl_consolidat e' from 'tbl_hold',
but working through the records in 'tbl_hold' in the order of dates in
a date field, rather than the order that the rows are necessarily in.
I came up with the following code to do this:
update tbl_consolidate
set field1 = b.field1, field2 = b.field2, field3 = b.field3
from
(select * from tbl_hold order by datefield1) b
where tbl_consolidate .ID1 = b.ID1
(I originally tried to use an alias 'a' for tbl_consolidate but this
threw an error)
In tbl_consolidate , ID1 is unique, but in tbl_hold there can be many
records with the same value in ID1. Using my code, I'd expect the
UPDATE to work its way through the records in tbl_hold in order of the
datefield1 column, but it doesn't seem to do it in this order. Can
anyone help?
I'm afraid that this does not make any sense at all. A table is an
unordered set of data, and an UPDATE statement will access rows in
order that the optimizer estimates to be the most effecient.
If I am to make a complete guess, this may be what you want:
UPDATE tbl_consolidate
SET field1 = h.field1, field2 = h.field2, field3 = h.field3
FROM tbl_consolidate c
JOIN tbl_hold h ON c.ID1 = h.ID1
JOIN (SELECT ID1, datefield1 = MAX(datefield1)
FROM tbl_hold
GRUOP BY ID1) AS m ON h.ID1 = m.ID1
AND h.datefield1 = m.datefield1
This will set tbl_consolidate to the latest values for each ID.
If this does not meet your requirements, please post the following:
o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of your business problem.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
