如何根據另一個表中的資料更新1個表中的附加欄位？

我有 2 個臨時表#tempbackfilltable和#temptablelive. 回填表的條目比活動表的多。在交叉參考兩個表并使用此代碼獲取其他條目后：

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';

我想在#tempbackfilltable使用現有資料時更新其中一列（DBTimestamp） #temptablelive。我要更新的現有資料是#temptablelive. 我嘗試運行這樣的東西：

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'

但是，這給了我 0 行。我也試過

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';

但這給了我一個錯誤，說 b.DBTimestamp 無法系結。

舉例說明：
#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

如您所見，即使 DBTimeStamp 中的日期是正確的，但時間已關閉，因此我想根據#temptablelive 更新時間。

#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

uj5u.com熱心網友回復：

您可以對匹配和不匹配的行分別使用兩個更新。或者，您可以使用 MERGE 指定不匹配的源從源的相應行集中獲取最大 DBTimeStamp

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)
;

uj5u.com熱心網友回復：

首先測驗您的查詢并檢查新舊值，如下所示：

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'

解決問題后（b.DBTimestamp 不能為空），然后將查詢更改為更新陳述句。

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'

uj5u.com熱心網友回復：

檢查這個查詢

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'

標籤：sql sql服务器

上一篇：在新列中顯示包含資訊的列

下一篇：如何在Tkinter/SQL中基于另一個變數選擇一個變數？