再帰クエリで UPDATE してみる

SQL Server

ネタ元：別テーブルの値で文字列を置換えしてUPDATE - Database Expert会議室


ネタ元で スカラ値関数 を使ったクエリを載せた理由は、再帰で実装したクエリを誰かが載せてくれたらいいなぁ〜という淡い期待からです。
ただ、そのままクローズした様なので、自分で調べてみました。
遠い昔に ORACLE で再帰クエリを書いた記憶はあるんですが、SQL Server だと無かったので初チャレンジ！
※ORACLE と書き方が違うんですね〜、CONNECT BY 〜 を使った気がする。


SQL SERVER で 再帰クエリを使うには、ここらへんが参考になりそうです。
共通テーブル式を使用する再帰クエリ
WITH common_table_expression (Transact-SQL)
WITH が追加されたのは知ってたんですが(使った事無いけど)、これで再帰を実現するようです。

CREATE TABLE [dbo].[Table_1](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [val] [nvarchar](max) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Table_1] ON
INSERT [dbo].[Table_1] ([id], [val]) VALUES (1, N'abcdefghij')
INSERT [dbo].[Table_1] ([id], [val]) VALUES (2, N'agnrfmlmn')
SET IDENTITY_INSERT [dbo].[Table_1] OFF
GO
CREATE TABLE [dbo].[Table_2](
 [mae] [nvarchar](1) NOT NULL,
 [ato] [nvarchar](1) NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table_2] ([mae], [ato]) VALUES (N'a', N'b')
INSERT [dbo].[Table_2] ([mae], [ato]) VALUES (N'b', N'c')
INSERT [dbo].[Table_2] ([mae], [ato]) VALUES (N'c', N'd')
INSERT [dbo].[Table_2] ([mae], [ato]) VALUES (N'd', N'e')
INSERT [dbo].[Table_2] ([mae], [ato]) VALUES (N'e', N'f')
INSERT [dbo].[Table_2] ([mae], [ato]) VALUES (N'f', N'g')
GO
-- ここまでがテーブルとデータ作成部分
SELECT * FROM [Table_1]

BEGIN TRAN;

WITH [CTE] ([id], [repVal], [currentNum]) AS (
  SELECT 
    [Table_1].[id], 
    REPLACE([Table_1].[val], [一行取得].[mae], [一行取得].[ato]) AS [repVal], 
    [一行取得].[currentNum] 
  FROM [Table_1], (
    SELECT [mae], [ato], [ROWNUM], MIN([ROWNUM]) AS [currentNum] 
    FROM (
      SELECT [mae], [ato], ROW_NUMBER () OVER (ORDER BY [mae]) AS [ROWNUM] 
      FROM [Table_2] 
    ) AS [TMP]
    WHERE [ROWNUM] = 1
    GROUP BY [mae], [ato], [ROWNUM]
  ) AS [一行取得]
  UNION ALL 
  SELECT 
    [CTE].[id], 
    REPLACE([CTE].[repVal], A.[mae], A.[ato]) AS [repVal], 
    [CTE].[currentNum] + 1 AS [currentNum] 
  FROM (
    SELECT [mae], [ato], ROW_NUMBER () OVER (ORDER BY [mae]) AS [ROWNUM] 
    FROM [Table_2] 
  ) AS A INNER JOIN [CTE] ON ( 
    [CTE].[currentNum] + 1 = A.[ROWNUM] 
  )
) 
UPDATE [Table_1] 
SET [val] = ( 
  SELECT TOP 1 [repVal] 
  FROM [CTE] 
  WHERE [CTE].[id] = [Table_1].[id] 
  ORDER BY [currentNum] DESC 
) 

SELECT * FROM [Table_1]
ROLLBACK

GO
DROP TABLE [Table_2]
DROP TABLE [Table_1]
GO

最適かどうかは分かりませんが、ネタ元に貼り付けた スカラ値関数 を使ったクエリと同じ結果になりますね。


しかし、「こうすれば実現出来る！」っていう発想が出てくるのが凄いなぁ。再帰で UPDATE っていう発想が無かった。
再帰SQL のイメージって 階層構造 を取得する*1位しか思いつかない。勉強になりました。

*1:SQL SERVER 2008 から Hierarchyid 型が追加されているので、再帰する必要が無くなったはず。これも使った事無い…