88409


**環境:**  
SQL Server 2016

MILKテーブルに、TEAのレコード全てを管理Aと連番が一致すれば UPDATE を、一致しなければ 管理Aの区分で 既存の連番に追加する形で採番をして
INSERT をしたいのですが、MERGEで上手くいきません。どうすればよいでしょうか？

テーブル[ **MILK** ]

管理A | 連番 | 品物  
---|---|---  
1001 | 001 | りんご  
1001 | 002 | ばなな  
1002 | 001 | りんご  
  
テーブル[ **TEA** ]

管理A | 連番 | 品物  
---|---|---  
1001 | NULL | みかん  
1001 | NULL | メロン  
1002 | NULL | ナシ  
1001 | 002 | スイカ  
  
* * *

結果テーブル[ **MILK** ]

管理A | 連番 | 品物  
---|---|---  
1001 | 001 | りんご  
1001 | 002 | スイカ  
1002 | 001 | りんご  
1001 | 003 | みかん  
1001 | 004 | メロン  
1002 | 002 | ナシ  
  
こうしたいのですが。  
みかんとメロンの採番が同じ003になってしまいます。

管理A | 連番 | 品物  
---|---|---  
1001 | 003 | みかん  
1001 | 003 | メロン  
  
* * *

駄目だったコード。

[code]

    MEAGE MILK AS A
    USING TEA AS B
    ON (A.[管理A] = B.[管理A] AND A.連番 = B.連番)
    WHEN MATCHED THEN 
    UPDATE SET  A.品物 = B.品物
    WHEN NOT MATCHED THEN 
    INSERT ( [管理A],
             [連番],
             [品物] )
    VALUES ( B.[管理A],
             (SELECT MAX([C.連番]) + 1 FROM MILK AS C WHERE B.[管理A] = C.[管理A] GROUP BY C.[管理A]),
             B.品物]    );
    
[/code]


CC BY-SA 4.0
2022-04-18T06:49:14.927
88377
2022-04-21T05:45:27.850
2022-04-18T06:56:02.390
3060
51081
question
sql sql-server
あれば更新、無ければ追加のSQLでInsert時に追加採番を行いたい。


書いてみました。

## テスト用テーブル

（一時テーブルにしているのは終わった後消さなくて良いから楽だなというだけです。普通のテーブルでもSQLは同じです）

[code]

    -- テスト用 ローカル一時テーブル
    DROP TABLE IF EXISTS #TEMP_MILK;
    DROP TABLE IF EXISTS #TEMP_TEA;
    
    SELECT *
    INTO #TEMP_MILK
    FROM(VALUES
      ('1001', '001', N'りんご'),
      ('1001', '002', N'ばなな'),
      ('1002', '001', N'りんご')
    ) Vals([管理A], [連番], [品物])
    ;
    
    SELECT *
    INTO #TEMP_TEA
    FROM(VALUES
      ('1001', NULL,  N'みかん'),
      ('1001', NULL,  N'メロン'),
      ('1001', NULL,  N'メロン'),
      ('1002', NULL,  N'ナシ'),
      ('1001', '002', N'スイカ')
    ) Vals([管理A], [連番], [品物])
    ;
    
[/code]

## TEA の内容 + α

  * 連番は [管理A] 単位で振る感じでしょうか。

  * 以下は「TEA の内容」プラス  
「"管理A" 単位で TEA に ROWNUM を振る」※ただしMILKに紐付く行がないもののみ  
「一致する "管理A" の MAX 連番も併せて取る」  
というクエリです。

これを MERGE の USING に入れます。

[code]

    SELECT
      t.*,
      (SELECT MAX(x.[連番]) FROM #TEMP_MILK x WHERE x.[管理A] = t.[管理A]) as MaxSeq  -- 管理A の MAX 連番
    FROM (
    
      -- MILK に紐付かない行（RowNum を振る）
      SELECT
        *, ROW_NUMBER() OVER(PARTITION BY t.[管理A] ORDER BY t.[連番]) as RowNum
      FROM #TEMP_TEA t
      WHERE NOT EXISTS (
        select top 1 1 from #TEMP_MILK x where x.[管理A] = t.[管理A] and x.[連番] = t.[連番]
      )
    
      union all
    
      -- MILK に紐付く行（RowNum は不要）
      SELECT
        *, NULL as RowNum
      FROM #TEMP_TEA t
      WHERE EXISTS (
        select top 1 1 from #TEMP_MILK x where x.[管理A] = t.[管理A] and x.[連番] = t.[連番]
      )
    
    ) t
    
    
[/code]

## MERGE

[code]

    MERGE INTO
      #TEMP_MILK AS A
    USING (  --★ USING 差し替え
    
      SELECT
        t.*,
        (SELECT MAX(x.[連番]) FROM #TEMP_MILK x WHERE x.[管理A] = t.[管理A]) as MaxSeq  -- 管理A の MAX 連番
      FROM (
    
        -- MILK に紐付かない行（RowNum を振る）
        SELECT
          *, ROW_NUMBER() OVER(PARTITION BY t.[管理A] ORDER BY t.[連番]) as RowNum
        FROM #TEMP_TEA t
        WHERE NOT EXISTS (
          select top 1 1 from #TEMP_MILK x where x.[管理A] = t.[管理A] and x.[連番] = t.[連番]
        )
    
        union all
    
        -- MILK に紐付く行（RowNum は不要）
        SELECT
          *, NULL as RowNum
        FROM #TEMP_TEA t
        WHERE EXISTS (
          select top 1 1 from #TEMP_MILK x where x.[管理A] = t.[管理A] and x.[連番] = t.[連番]
        )
    
      ) t
    
    ) AS B ON (
      A.[管理A] = B.[管理A] AND A.連番 = B.連番
    )
    WHEN MATCHED THEN 
      UPDATE SET  A.品物 = B.品物
    WHEN NOT MATCHED THEN 
      INSERT (
        [管理A],
        [連番],
        [品物]
      )
      VALUES (
        B.[管理A],
        B.[MaxSeq] + B.RowNum,  --★差し替え
        B.[品物]
      );
    
[/code]

管理A | 連番 | 品物  
---|---|---  
1001 | 001 | りんご  
1001 | 002 | スイカ  
1002 | 001 | りんご  
1001 | 3 | みかん  
1001 | 4 | メロン  
1001 | 5 | メロン  
1002 | 2 | ナシ  
  
ゼロ埋めはされてませんけども。こんな感じでしょうか。


CC BY-SA 4.0
2022-04-20T03:37:06.467
88409
2022-04-21T05:45:27.850
2022-04-21T05:45:27.850
51238
51238
88377
answer