在没有好的连接键的情况下，在更新过程中强制使用唯一值

Question

我有一个表"MyTable"，有3列:

• FilePath
• <
• 状态/gh>

我的表行示例:

FilePath | FileName | Status
c:temp  | Null     | 30
c:temp  | Null     | 30
c:temp  | Null     | 30
c:temp  | Null     | 30
c:temp  | a.csv    | 40
c:temp  | b.csv    | 40
c:temp  | c.csv    | 40
c:temp  | d.csv    | 40

我想更新行FileName为空，行FileName有值。

现在，下面的查询将用相同的值更新所有的空值。

我需要强制更新在更新

期间每行选择一个唯一的值

UPDATE  t1
SET t1.FileName = t2.FileName
FROM MyTable t1
JOIN MyTable t2 ON t1.FilePath = t2.FilePath

当前更新将给出以下结果:

FilePath | FileName | Status
c:temp  | a.csv    | 30
c:temp  | a.csv    | 30
c:temp  | a.csv    | 30
c:temp  | a.csv    | 30
c:temp  | a.csv    | 40
c:temp  | b.csv    | 40
c:temp  | c.csv    | 40
c:temp  | d.csv    | 40

我需要的是:

FilePath | FileName | Status
c:temp  | a.csv    | 30
c:temp  | b.csv    | 30
c:temp  | c.csv    | 30
c:temp  | d.csv    | 30
c:temp  | a.csv    | 40
c:temp  | b.csv    | 40
c:temp  | c.csv    | 40
c:temp  | d.csv    | 40

Answer 1

假设您正在使用一个相当现代的SQL Server版本，并且您希望FileName模式重新开始，并根据需要为每个具有NULL FileName值的状态重复:

-- Sample data.
declare @MyTable as Table ( Id Int Identity, FilePath VarChar(16), FileName VarChar(16), Status Int );
insert into @MyTable ( FilePath, FileName, Status ) values
  ( 'c:temp', null, 30 ),
  ( 'c:temp', null, 30 ),
  ( 'c:temp', null, 30 ),
  ( 'c:temp', null, 30 ),
  ( 'c:temp', 'a.csv', 40 ),
  ( 'c:temp', 'b.csv', 40 ),
  ( 'c:temp', 'c.csv', 40 ),
  ( 'c:temp', 'd.csv', 40 ),
  ( 'c:temp', null, 50 ),
  ( 'c:temp', null, 60 ),
  ( 'c:temp', null, 60 ),
  ( 'c:temp', null, 60 ),
  ( 'c:temp', null, 60 ),
  ( 'c:temp', null, 60 ),
  ( 'c:temp', null, 60 ),
  ( 'c:temp', null, 60 );
select * from @MyTable;
-- Update the   null   values.
with
  FileNames as (
    select FileName, Row_Number() over ( order by FileName ) - 1 as RN
      from @MyTable
      where FileName is not NULL ),
  NullNames as (
    select Id, Row_Number() over ( partition by Status order by FileName ) - 1 as RN
      from @MyTable
      where FileName is NULL )
  update MT
    set FileName = FN.FileName
    from @MyTable as MT inner join
      NullNames as NN on NN.Id = MT.Id inner join
      FileNames as FN on FN.RN = NN.RN % ( select count(42) from FileNames );
select * from @MyTable;