|
Home > Archive > Microsoft SQL Server forum > June 2005 > T-SQL UPDATE .. FROM .. ORDER BY problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
T-SQL UPDATE .. FROM .. ORDER BY problem
|
|
| champ.supernova@gmail.com 2005-06-30, 9:23 am |
| Hi,
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?
| |
| Erland Sommarskog 2005-06-30, 9:23 am |
| (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/techin.../2000/books.asp
| |
| champ.supernova@gmail.com 2005-06-30, 9:23 am |
| Sorry, you're right. I've just realised what I'm doing wrong...
|
|
|
|
|