通过连接到满足某些条件的另一个表的最新记录来更新一个表

I'm trying to avoid the urge to use a loop for this solution and instead use a set-based operation.

我试图避免对这个解决方案使用循环，而是使用基于集合的操作。

I need to update each record in a table based on the most recent occurrence of a join to another table. My first thought was to join to the table that I need the "most recent date" from and group on the join conditions, but using GROUP BY with UPDATE is invalid.

我需要基于最近发生的另一个表的连接更新表中的每个记录。我的第一个想法是加入到表中，我需要来自和组的“最近的日期”，但是使用group BY with UPDATE是无效的。

Here's what I have so far. I have a feeling this will be solved with a CTE...

这是我到目前为止所得到的。我有种感觉，用CTE就能解决这个问题……

CREATE TABLE Part (
    Area            VARCHAR(10) NOT NULL,
    SequenceNumber  INT         NOT NULL,
    DateAdded       DATETIME    NOT NULL
);
INSERT INTO Part VALUES
('A', 1, '2018-04-01'),
('A', 1, '2018-04-02'),
('A', 2, '2018-04-03'),
('A', 2, '2018-04-04'),
('B', 2, '2018-04-04'),
('B', 2, '2018-04-03'),
('B', 3, '2018-04-02'),
('B', 3, '2018-04-01');

DECLARE @Filters    TABLE
(
    Area            VARCHAR(10),
    StartingSeqNum  INT,
    StartTime       DATETIME 
);
INSERT INTO @Filters VALUES
('A', 2, NULL),
('B', 3, NULL);

-- Update each StartTime in @Filters with the most recent
-- DateAdded of the corresponding SequenceNumber in the Part table per Area.
UPDATE f
SET f.StartTime = MAX(p.DateAdded)
FROM
    @Filters f
    INNER JOIN Part p ON p.Area = f.Area
        AND p.SequenceNumber = f.StartingSeqNum
GROUP BY
    p.Area,
    p.SequenceNumber;

Based on the sample data above, the operation should populate the following values in @Filters:

基于上面的示例数据，操作应该在@Filters中填充以下值:

('A', 2, '2018-04-04')
('B', 3, '2018-04-02')

2 个解决方案

#1

You can use a CTE to do this, as so:

你可以用CTE来做这件事:

;with maxParts as
(
    select Area, SequenceNumber, MAX(dateadded) as maxDateAdded 
    from Part
    group by Area, SequenceNumber
)
UPDATE f
SET f.StartTime = p.maxDateAdded
from @Filters f
    inner join maxParts p 
        on f.Area = p.Area
        and f.StartingSeqNum = p.SequenceNumber

However, you can also rewrite the CTE as a subquery:

但是，您也可以将CTE重写为子查询:

UPDATE f
SET f.StartTime = p.maxDateAdded
from @Filters f
    inner join (
            select Area, SequenceNumber, MAX(dateadded) as maxDateAdded 
            from Part
            group by Area, SequenceNumber
        ) p 
        on f.Area = p.Area
        and f.StartingSeqNum = p.SequenceNumber

The query plans for these should be identical - all the CTE really allows you is cleaner-looking queries and the ability to easily reuse that SELECT.

这些查询计划应该是相同的——所有CTE真正允许您进行更清晰的查询，并能够轻松重用该选择。

#2

(a)

(一)

UPDATE Filters
       SET Filters.StartTime = (SELECT max(Part.DateAdded)
                                FROM Part
                                WHERE Part.Area = Filters.Area
                                      AND Part.SequenceNumber = Filters.StartingSeqNum)
       FROM @Filters Filters;

(b)

UPDATE Filters
       SET Filters.StartTime = X.StartTime
       FROM @Filters Filters
            INNER JOIN (SELECT max(Part.DateAdded) StartTime,
                               Part.Area,
                               Part.SequenceNumber
                               FROM Part
                               GROUP BY Part.Area,
                                        Part.SequenceNumber) X
                       ON X.Area = Filters.Area
                          AND X.SequenceNumber = Filters.StartingSeqNum;

Both, (a) and (b) should do what you want. I'm not sure whats better. Maybe (b) can reuse the subquery relation and therefore costs less overall.

(a)和(b)都应该做你想做的事。我不知道什么更好。也许(b)可以重用子查询关系，因此总体成本更低。

#1