sql server (alternate): Given the table (mytable)
my_id int (pk)
my_type char(1)
my_version tinyint
my_datetime datetime

Example data
1 a 1 1/1/03
2 b 1 1/2/03
3 c 1 1/3/03
4 d 1 1/4/03
5 e 1 1/5/03
6 a 2 null
7 b 2 1/5/03
8 c 2 null
9 d 2 1/5/03
10 e 2 1/6/03

I want to write an update statement that will set all version 2
datetimes to their version 1 value when the version 2 value is null

After the update the data should look like:
Example data
1 a 1 1/1/03
2 b 1 1/2/03
3 c 1 1/3/03
4 d 1 1/4/03
5 e 1 1/5/03
6 a 2 1/1/03
7 b 2 1/5/03
8 c 2 1/3/03
9 d 2 1/5/03
10 e 2 1/6/03

I've tried:

update my_table
set my_datetime = v1.my_datetime
from
(select *
from my_table
where my_version = 1 and my_datetime is not null) v1,
(select *
from my_table
where my_version = 2 and my_datetime is null) v2
where v1.my_type = v2.my_type

But this just updates all version 2 rows to the lowest date.

What am I doing wrong???

[quoted text, click to view]

David, yes you can assume that. There are more versions than just 1 &
2 though so I modified your where statement to add:
UPDATE MyTable
SET my_datetime =
(SELECT my_datetime
FROM MyTable AS M
WHERE my_version = 1
AND my_type = MyTable.my_type)
WHERE my_datetime IS NULL and my_version = 2

Can I assume that there is only one row where my_version=1 for each value of
my_type? If so:

UPDATE MyTable
SET my_datetime =
(SELECT my_datetime
FROM MyTable AS M
WHERE my_version = 1
AND my_type = MyTable.my_type)
WHERE my_datetime IS NULL

--
David Portas
------------
Please reply only to the newsgroup
--