Posted by Erland Sommarskog on 06/30/05 16:24

(champ.supernova@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_consolidate' 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, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]