Updating a SQL table with values from different rows in itself

I have a table

with 5 columns :-

• Country Code
• Language
• Warehouse
• ActiveFrom
• ActiveTo

A subset of data (apologies for the column headers being out of alignment:-

Country
Code
   Language 
      Warehouse
                    ActiveFrom          ActiveTo
AT  de  BHU 2011-08-08 00:00:00.000 9999-12-31 23:59:59.000
AT  de  WGN 2011-02-14 00:00:00.000 9999-12-31 23:59:59.000
BE  fr  BHU 2011-09-01 00:00:00.000 9999-12-31 23:59:59.000
BE  fr  WGN 2011-02-14 00:00:00.000 9999-12-31 23:59:59.000
CH  de  WGN 2011-02-14 00:00:00.000 9999-12-31 23:59:59.000
CZ  sk  BHU 2011-08-08 00:00:00.000 9999-12-31 23:59:59.000
CZ  sk  WGN 2011-02-14 00:00:00.000 9999-12-31 23:59:59.000
DE  de  BHU 2011-09-01 00:00:00.000 9999-12-31 23:59:59.000
DE  de  WGN 2011-02-14 00:00:00.000 9999-12-31 23:59:59.000

What I want to do, is update the ActiveTo column, to the value of the ActiveFrom Date (give or take a few milliseconds) for the same warehouse.

I've tried This :-

update Translations
 set ActiveTo = DateAdd(ms, -3, ot.ActiveFrom)
    from Translations ot
   Where Warehouse = 'WGN'
     and CountryCode = ot.CountryCode

but, it gives these results.

CountryCode Language    Warehouse   ActiveFrom  ActiveTo
AT  de  BHU 2011-08-08 00:00:00.000 9999-12-31 23:59:59.000
AT  de  WGN 2011-02-14 00:00:00.000 2011-02-13 23:59:59.997
BE  fr  BHU 2011-09-01 00:00:00.000 9999-12-31 23:59:59.000
BE  fr  WGN 2011-02-14 00:00:00.000 2011-02-13 23:59:59.997
CH  de  WGN 2011-02-14 00:00:00.000 2011-02-13 23:59:59.997
CZ  sk  BHU 2011-08-08 00:00:00.000 9999-12-31 23:59:59.000
CZ  sk  WGN 2011-02-14 00:00:00.000 2011-02-13 23:59:59.997
DE  de  BHU 2011-09-01 00:00:00.000 9999-12-31 23:59:59.000
开发者_C百科DE  de  WGN 2011-02-14 00:00:00.000 2011-02-13 23:59:59.997

It's updating the right rows. But the value is wrong, it's deducting 3 milliseconds from its own ActiveFrom Date, instead of the other warehouse start date.

This SQL gives the right results in a derived table:-

select t.CountryCode, t.Warehouse, DateAdd (ms, -3, ot.ActiveFrom) as 'TransferToBHU', t.ActiveFrom, t.ActiveTo,
   ot.CountryCode, ot.Warehouse, ot.ActiveFrom, ot.ActiveTo
from Translations t
inner join Translations ot
  on ot.CountryCode= t.CountryCode

Where t.Warehouse = 'WGN' and ot.Warehouse = 'BHU' Order by t.CountryCode

    CountryCode Warehouse   TransferToBHU   ActiveFrom  ActiveTo    CountryCode Warehouse   ActiveFrom  ActiveTo
AT  WGN 2011-08-07 23:59:59.997 2011-02-14 00:00:00.000 2011-02-13 23:59:59.997 AT  BHU 2011-08-08 00:00:00.000 9999-12-31 23:59:59.000
BE  WGN 2011-08-31 23:59:59.997 2011-02-14 00:00:00.000 2011-02-13 23:59:59.997 BE  BHU 2011-09-01 00:00:00.000 9999-12-31 23:59:59.000
CZ  WGN 2011-08-07 23:59:59.997 2011-02-14 00:00:00.000 2011-02-13 23:59:59.997 CZ  BHU 2011-08-08 00:00:00.000 9999-12-31 23:59:59.000
DE  WGN 2011-08-31 23:59:59.997 2011-02-14 00:00:00.000 2011-02-13 23:59:59.997 DE  BHU 2011-09-01 00:00:00.000 9999-12-31 23:59:59.000

Now, if I could get the calculated value in 'TransferToBHU' into the update value, that's exactly what I want.

The simplest solution would update the 'WGN' warehouse values based on the 'BHU' values. However, the best would update based on getting the two latest ActiveFrom dates for a given CountryCode.

Either solution is acceptable, although if anyone supplies one that works on the dates - they will get the accepted answer.

Thanks in advance.

update t1
set ActiveTo = DateAdd(ms, -3, t2.ActiveFrom)
from Translations t1
   JOIN
   Translations t2 ON t1.CountryCode = t2.CountryCode
Where t1.Warehouse = 'WGN' AND t2.Warehouse = 'BHU'

Of course, country CH has no BHU row, so:

update t1
set ActiveTo = DateAdd(ms, -3, ISNULL(t2.ActiveFrom,t1.ActiveFrom))
from Translations t1
   LEFT JOIN
   Translations t2 ON t1.CountryCode = t2.CountryCode
Where t1.Warehouse = 'WGN' AND t2.Warehouse = 'BHU'