How do I update the additional fields from 1 table based on the data from another table?-CodePudding

I have 2 temp tables #tempbackfilltable and #temptablelive. The backfill table has more entries than the live table. After cross referencing both tables and getting the additional entries using this code:

SELECT *
FROM #tempbackfilltable a
WHERE NOT EXISTS (SELECT *
                  FROM #temptablelive b
                  WHERE a.DataDate = b.DataDate
                    AND a.StockCode = b.StockCode)
  AND DataDate >= '2021-08-04';

I want to update one of the columns (DBTimestamp) in #tempbackfilltable using existing data in #temptablelive. The existing data I would like to update is the min(DBTimestamp) for the day in #temptablelive. I tried running something like this:

Update a 
set a.DBTimestamp = b.DBTimestamp
FROM #tempbackfilltableShaun a
RIGHT JOIN #tempbackfilltable b
ON  a.DataDate = b.DataDate AND a.StockCode = b.StockCode
Where NOT EXISTS 
(SELECT * FROM #tempbackfilltable b WHERE a.DataDate = b.DataDate AND a.StockCode = b.StockCode)
and a.DataDate >= '2021-08-04'

However, this gives me 0 rows. I also tried

Update #tempbackfilltableShaun 
set #tempbackfilltableShaun.DBTimestamp = b.DBTimestamp
FROM #tempbackfilltableShaun a WHERE NOT EXISTS 
(SELECT * FROM #tempbackfilltable b WHERE a.DataDate = b.DataDate AND a.StockCode = b.StockCode)
and DataDate >= '2021-08-04';

but this gave me an error saying b.DBTimestamp cannot be bound.

For illustration:
#temptablelive

DataDate   | StockCode | DBTimeStamp 
2021-12-10   688396      2021-12-10 03:22:04.000
2021-12-10   688399      2021-12-10 03:22:04.000
2021-12-10   688568      2021-12-10 03:22:04.000
2021-12-10   688981      2021-12-10 03:22:04.000
2021-12-13   688577      2021-12-13 03:10:23.000

#tempbackfilltable

DataDate   | StockCode | DBTimeStamp 
2021-12-10   688396      2021-12-10 00:00:00.000
2021-12-10   688399      2021-12-10 00:00:00.000
2021-12-10   688568      2021-12-10 00:00:00.000
2021-12-10   688981      2021-12-10 00:00:00.000
2021-12-13   688396      2021-12-13 00:00:00.000
2021-12-13   688505      2021-12-13 00:00:00.000
2021-12-13   688599      2021-12-13 00:00:00.000

As you can see, even though the date is correct in DBTimeStamp, the time is off so I would like to update the time based on the #temptablelive.

Desired output on #tempbackfilltable

DataDate   | StockCode | DBTimeStamp 
2021-12-10   688396      2021-12-10 03:22:04.000
2021-12-10   688399      2021-12-10 03:22:04.000
2021-12-10   688568      2021-12-10 03:22:04.000
2021-12-10   688981      2021-12-10 03:22:04.000
2021-12-13   688396      2021-12-13 03:10:23.000
2021-12-13   688505      2021-12-13 03:10:23.000
2021-12-13   688599      2021-12-13 03:10:23.000

CodePudding user response：

You can use two updates for matching and non-matching rows respectivley. Alternativevly you can use MERGE specifying that unmatched souce gets max DBTimeStamp from the respective set of rows from the source

merge #tempbackfilltable b
using #temptablelive a
   on a.DataDate = b.DataDate AND a.StockCode = b.StockCode and a.DataDate >= '2021-08-04'
when matched then
   update set DBTimeStamp = a.DBTimeStamp
when not matched by source then
   update set DBTimeStamp = (select max(DBTimeStamp) 
                             from #temptablelive c
                             where b.DataDate = c.DataDate)
;

CodePudding user response：

First test your query and check old and new value like this:

SELECT a.DBTimestamp, b.DBTimestamp
FROM #tempbackfilltable a
RIGHT JOIN #temptablelive b
ON  a.DataDate = b.DataDate AND a.StockCode = b.StockCode
Where NOT EXISTS 
(SELECT * FROM #temptablelive b WHERE a.DataDate = b.DataDate AND a.StockCode = b.StockCode)
and a.DataDate >= '2021-08-04'

After you resolve the problem (b.DBTimestamp must not be null), then change the query to update statment.

Update a 
set a.DBTimestamp = b.DBTimestamp
FROM #tempbackfilltable a
RIGHT JOIN #temptablelive b
ON  a.DataDate = b.DataDate AND a.StockCode = b.StockCode
Where NOT EXISTS 
(SELECT * FROM #temptablelive b WHERE a.DataDate = b.DataDate AND a.StockCode = b.StockCode)
and a.DataDate >= '2021-08-04'

CodePudding user response：

check this query

UPDATE #tempbackfilltable a
SET a.DBTimestamp = b.DBTimestamp
JOIN (select  min(DBTimestamp)  DBTimestamp , DataDate,StockCode from #temptablelive
group by DataDate,StockCode )  b
ON  a.DataDate = b.DataDate AND a.StockCode = b.StockCode
Where NOT EXISTS 
(SELECT * FROM #temptablelive c WHERE a.DataDate = c.DataDate AND a.StockCode = c.StockCode)
and a.DataDate >= '2021-08-04'