SQL Update值由同一个表的其他值组成

I have a table where I have FKs to other tables and timestamps for each record in this table. For example:

我有一个表,其中我有FK到其他表和此表中的每个记录的时间戳。例如:

ID    FK    Timestamp
1     1     2015-05-05
2     1     2015-05-06
3     1     2015-05-07
4     2     2015-04-08
5     2     2015-04-09
6     2     2020-04-10 <- this timestamp should be set to timestamp of 5. row
7     2     2015-04-12
8     3     2015-05-06

The problem is that some of the timestamps I got contains values from the future which should be set back to a normal timestamp. For example the one before this step, on the same FK.

问题是我得到的一些时间戳包含来自未来的值,应该将其设置回正常的时间戳。例如,在此步骤之前的那个,在相同的FK上。

I have a query that selects all records from the future, and gets their max timestamps as well (before the future one):

我有一个查询,从未来选择所有记录,并获得他们的最大时间戳(在未来之前):

select ws1.Id, ws1.Timestamp, ws1.workitem_id,
    (select max(Timestamp) from WorkSteps where WorkSteps.WorkItem_Id = ws1.WorkItem_Id AND Timestamp<GETDATE() AND worksteps.Id<ws1.id)
from worksteps ws1 
where ws1.Timestamp > GETDATE()
order by WorkItem_Id;

How can I transform this into an update?

如何将其转换为更新?

Update

Ok, I think I got it:

好的,我想我明白了:

update worksteps set Timestamp = 
    (select max(Timestamp) from WorkSteps ws1 where ws1.WorkItem_Id = worksteps.WorkItem_Id AND Timestamp<GETDATE() AND ws1.Id<worksteps.id)
where worksteps.Timestamp > GETDATE();

1 个解决方案

#1

You can make your query into a derived table or CTE and join to it in an UPDATE:

您可以查询派生表或CTE并在UPDATE中加入它:

UPDATE t
SET TimeStamp=q.{The Alias you need to provide for your sub-select}
FROM worksteps t
INNER JOIN (Your Query) q
  ON t.{ThePrimaryKey}=q.{ThePrimaryKey}

This is psuedocode, obviously: you need to replace the parts in curly-braces with the actual values they describe.

这显然是伪代码:你需要用它们描述的实际值替换大括号中的部分。

#1