在Col3的基础上更新具有Distinct col1、Min(Col2)和Col3的表

Question

在我的MeterReading表中，我有

-Id(Primary)
-ProjectMeterId
-MeterRead
-ReadDate
-ReadCount

对于给定的ProjectMeterId，我有多个条目。我只想更新ReadDate最小的那些行的ReadCount（对于给定的ProjectMeterId），即

  Update MeterReading
  set ReadCount = 1234
  where  (ReadDate is minimun for a given ProjectMeterId)

注意：表中有许多ProjectMeterId，因此必须更新多个值。在不声明任何SP、表变量和所有变量的情况下，我应该如何在查询中执行此操作？？因为这样做，更新所有条目需要10分钟以上的时间。

这就是我现在正在做的：

 UPDATE TTable
SET TTable.ReadCount= 222   
From
(
  Select * From MeterReading where Id in 
  (
     Select Id From
     (
        SELECT Min(Id)as Id, MIN(ReadDate) as ReadDate, ProjectMeterId  FROM MeterReading 
        WHERE ProjectMeterId IS NOT NULL AND ProjectId IS NOT NULL Group By ProjectMeterId 
     )  as temp1
 )
 ) TTable, 
(
     Select * From MeterReading where Id in
          (
        Select Id From 
         (
        SELECT MIN(ReadDate) as ReadDate, ProjectMeterId  FROM MeterReading 
        WHERE ProjectMeterId IS NOT NULL AND ProjectId IS NOT NULL Group By ProjectMeterId 
     ) as temp2
   )
) STable
Where STable.ProjectMeterId = TTable.ProjectMeterId

但是subQueries中的Min（id）给了我一行，该行具有特定于ProjectMeterId的最小id，而不是ReadDate。

我该怎么办？

Answer 1

;WITH CTE
     AS (SELECT *,
                MIN(ReadDate) OVER (PARTITION BY ProjectMeterId) AS Mn
         FROM   MeterReading)
UPDATE CTE
SET    ReadCount = 1234
WHERE  ReadDate = Mn  

或

UPDATE m1
SET    ReadCount = 1234
FROM   MeterReading m1
WHERE  NOT EXISTS(SELECT *
                  FROM   MeterReading m2
                  WHERE  m1.ProjectMeterId = m2.ProjectMeterId
                         AND m2.ReadDate < m1.ReadDate)