PostgreSQL INによるサブクエリについて

Question

準備

テーブルの作成

CREATE TABLE test (id1 TEXT, id2 TEXT, id3 TEXT, data TEXT);

適当なデータを数件入れておく。

SQLでの期待処理

id1='A' AND id2='B' AND id3='C' AND data=''
のデータのうち最大5件のdataを'ABC'にする。
(データ抽出順番は制約なし。)

質問

上記の期待処理をするときに、LIMITというのを使って件数を制限すること、UPDATEではLIMITを使えないためサブクエリのSELECTでLIMITを使うことを知りました。
ネット記事を参考にSQLを作ろうと思ったのですが、下記SQLの???に何を指定してよいか分かりません。

テーブルに設定している主キーでしょうか？
主キーを設定していない場合、何を設定すればよいでしょうか？(id1, id2, id3)でしょうか。

回答よろしくお願いします。

UPDATE test
SET data = 'ABC' 
WHERE ??? IN(
    SELECT ??? FROM test
    WHERE id1 = 'A' AND id2 = 'B' AND id3 = 'C' AND data = ''
    LIMIT 5
);

参考

Update文にてORDER BY句とLIMIT句を使いたくなった。

Comments

[hoshi-takanori]

PostgreSQL なら ctid を使えるかもしれませんが、本来は id を持たせるべきでしょうね…。
http://dbnote.web.fc2.com/note_datatype_systemcol1_oid_and_ctid.html

Answer 1

UPDATE文一発で実現するのは難しそうなので、たとえば以下のような手順ではどうでしょうか?

① 一時表temp_testを作成する。この表では(id1, id2, id3, data) = ('A', 'B', 'C', '')を満たすデータのうち、最大5件のdataが'ABC'に変更されている。

CREATE TEMP TABLE temp_test AS 
SELECT 
  id1, id2, id3,
  CASE WHEN (id1, id2, id3, data) = ('A', 'B', 'C', '') AND ROW_NUMBER() OVER (PARTITION BY id1, id2, id3, data) <= 5 THEN 'ABC' ELSE data END AS data
FROM test;

② test表のすべての行を削除する。

TRUNCATE TABLE test;

③ temp_test表の内容をすべてtest表に反映する。

INSERT INTO test SELECT * FROM temp_test;

Answer 2

テーブルに主キーを追加してしまうのがやりやすいんじゃないでしょうか

Comments

[person]

回答ありがとうございます。

回答的に、質問にあるSQLの???には主キーを指定するということで合っていますか？

[yuma.inaura]

そですね
動作が気になるのであればテスト用にテーブルを作るなどして試してみてください

[person]

回答遅くなり申し訳ないです。
挙動的には意図した結果になりました。