更新表中的列

1 回答

• 1

  您可以通过select语句更新现有数据 .

  在这里你选择所有C3和C4与你的条件和 UNION . 这个列表你必须通过 XML PATH 连接并构建你的 C_NEW 列 .

  UPDATE [Table]
      SET C_NEW = SelectTable.C_NEW
  FROM
  ( 
      SELECT [Table].c1, [Table].c2, c3, c4, SUBSTRING(t2.C_NEW , 2, LEN(t2.C_NEW)) AS C_NEW
      FROM dbo.[Table]
      LEFT JOIN (
      SELECT C1, C2, (
          (SELECT ', ' + C3 AS [text()]  FROM (
              SELECT C1, c2, C3 FROM [Table]
              WHERE C3 <> C1 AND C3 <> C2 AND C1 = t.C1 AND C2 = t.C2
              UNION 
              SELECT C1, c2, C4 FROM [Table]
              WHERE C4 <> C1 AND C1 = t.C1 AND C2 = t.C2
          ) AS t3 For XML PATH (''))
      )
      AS C_NEW
      FROM [Table] AS t
      GROUP BY C1, C2, t.C_NEW
      ) AS t2 ON t2.C1 = [Table].C1 AND t2.C2 = [Table].C2
  ) AS SelectTable
  WHERE [Table].C1 = SelectTable.C1 
      AND [Table].C2 = SelectTable.C2 
      AND [Table].C3 = SelectTable.C3
      AND [Table].C4 = SelectTable.C4
  

  回复于 2024-04-27T13:16:13+08:00