SQL更新记录的第一个实例- SQL Server

Question

我有一个导入过程，需要更新表中旧记录的"End Date"。导入来自其他人，我需要匹配特定用户信息的一些详细信息丢失了。我需要做的是更新旧的记录，在下面的例子中，它将是UserID = 1，而不是更新UserID = 4。

我有以下sql更新，但你可以看出，它更新两个记录:

UPDATE t1
SET t1.[EndDate] = t2.[EndDate]
FROM ExistingUser AS t1, ImportUser AS t2
WHERE (t2.[uName] = t1.[uName]) AND (t1.[EndDate] IS NULL);

免责声明:我没有创建数据库，也不能重新设计表，所以请同情我。谢谢! !

ExistingUser - Table

UserID    uName   BeginDate    EndDate
  1       John   01/01/2013
  2       Mary   05/01/2014   04/30/2015
  3       Bob    12/01/2014
  4       John   06/01/2015

uName    EndDate
John    05/31/2015

Answer 1

这可以通过构建一个子查询来完成，该子查询按未填充EndDate的用户名对条目进行编号。这个子查询看起来像这样:

SELECT [UserID], [uName],
  ROW_NUMBER() OVER (PARTITION BY [uName] ORDER BY [BeginDate]) AS IX
FROM ExistingUser
WHERE [EndDate] IS NULL

演示:http://www.sqlfiddle.com/# !6/72c49/2

| UserID | uName | IX |
|--------|-------|----|
|      3 |   Bob |  1 |
|      1 |  John |  1 |
|      4 |  John |  2 |

在此子查询中，John中具有相同uName的两条记录按BeginDate顺序编号。现在，如果我们将原始UPDATE查询连接到搜索IX=1记录的子查询中，我们将只更新最早的行。

WITH EarliestExistingUser AS (
  SELECT [UserID], [uName],
    ROW_NUMBER() OVER (PARTITION BY [uName] ORDER BY [BeginDate]) AS IX
  FROM ExistingUser
  WHERE [EndDate] IS NULL
)
UPDATE t1
SET t1.[EndDate] = t2.[EndDate]
FROM ExistingUser AS t1
  JOIN EarliestExistingUser AS t1f ON t1.UserID = t1f.UserID
,ImportUser AS t2
WHERE (t2.[uName] = t1.[uName]) AND (t1.[EndDate] IS NULL)
AND (t1f.IX = 1)

演示:http://www.sqlfiddle.com/# !6/72c49/3