时间戳

首页 > 解决方案 > 更新未添加正确的值

sql-server - 更新未添加正确的值

问题描述

我的 select 语句从我已正确加入的表中返回 id（393，从 92 开始并上升 484，它不是 PK 字段，因此某些 id 可以多次使用）我总共需要 550 行更新，当我运行更新时，它只会将数字 92 添加到所有 550 行的表中。

update movements set [location] = locaID.id FROM (
SELECT
      lo.id
  FROM [Harvest_Transactions] ht
  left join [Harvest_Master_Shift] hms on ht.Harvest_Master_id = hms.Harvest_Master_id
  left join [Greenhouse_Troughs] gt on ht.Trough = gt.Greenhouse_Trough_id 
  left join [batches] b on b.name = hms.Batch_id
  left join [phases] p on p.batch = b.id and p.[type] = 3
  left join #loc lo on lo.name = gt.Trough_No and lo.area = gt.SQM_per_Trough and lo.Bay_id = gt.Bay_id
  where ht.Number_of_Plants_Harvested != 0 and (hms.CreatedOn <= '2020-02-05 09:33:00.000' OR hms.CreatedOn is null )
 )locaID where product = 14

我错过了什么，所以它更新为正确的值？

标签： sql-server

解决方案

我对您的查询的第一印象是，只有移动表中 product = 14 的行才会使用来自子查询的单个值进行更新。

如果您想根据另一个派生表更新值，您需要某种方式将这些行链接在一起 - 即在您正在更新的表和保存其他数据的表之间建立一个 JOIN（请参见此处）

运动和运动之间的共同栏是什么

SELECT
  lo.id
 FROM [Harvest_Transactions] ht
 left join [Harvest_Master_Shift] hms on ht.Harvest_Master_id = hms.Harvest_Master_id
 left join [Greenhouse_Troughs] gt on ht.Trough = gt.Greenhouse_Trough_id 
 left join [batches] b on b.name = hms.Batch_id
 left join [phases] p on p.batch = b.id and p.[type] = 3
 left join #loc lo on lo.name = gt.Trough_No and lo.area = gt.SQM_per_Trough and 
 lo.Bay_id = gt.Bay_id
 where ht.Number_of_Plants_Harvested != 0 and (hms.CreatedOn <= '2020-02-05 
 09:33:00.000' OR hms.CreatedOn is null )

我认为粗略的语法应该是

UPDATE movements
set [location] = locaID.id 
FROM movements
JOIN (
SELECT
  lo.id
 FROM [Harvest_Transactions] ht
 left join [Harvest_Master_Shift] hms on ht.Harvest_Master_id = hms.Harvest_Master_id
 left join [Greenhouse_Troughs] gt on ht.Trough = gt.Greenhouse_Trough_id 
 left join [batches] b on b.name = hms.Batch_id
 left join [phases] p on p.batch = b.id and p.[type] = 3
 left join #loc lo on lo.name = gt.Trough_No and lo.area = gt.SQM_per_Trough and 
 lo.Bay_id = gt.Bay_id
 where ht.Number_of_Plants_Harvested != 0 and (hms.CreatedOn <= '2020-02-05 
 09:33:00.000' OR hms.CreatedOn is null )
) locaID
ON movements.<some column> = locaID.<some column>

推荐阅读