UPDATE 文中で使える LIMIT 相当構文を DBMS 毎に見てみよう

Oracle

create table test1 (id number);
I have put 20 different numbers into the table and now I
want to update the last 4 of them.
See below:
update (select id from test1 where id in (select id from (select id from test1 order by id desc) where rownum < 5)) set id = 100;
I have just ordered rows by descending manner, selected
last 4 and when have used IN operand to restrict TEST1 selection. Then it's easy to use subquery-update statement.

ROWNUM*1 を使った副問合せで、任意の件数を抽出し、その結果*2に対して更新をかけている。
しかし、これだけのことに、

UPDATE (SELECT id
        FROM test1
        WHERE id in (SELECT id
                     FROM (SELECT id
                           FROM test1
                           ORDER BY id DESC)
                     WHERE ROWNUM < 5) )
SET id = 100;

こんな仰々しい SQL を書いちゃうのもナンだかなァというところである。
Oracle であれば、PL/SQL 無名ブロックを用いて割と好きに書ける*3ので、よい子は素直にカーソルループで書く。

declare
  cursor cr as
    SELECT id_name,amount
    FROM ( SELECT id,name,amount
           FROM my_table
           ORDER BY amount )
    WHERE ROWNUM < 5
    FOR UPDATE;

begin
  for pnt in test loop
    UPDATE ... SET ...
    WHERE CURRENT OF cr;
  end loop;
end;

DB2

Oracle 同様、クエリに対する更新ができる。

UPDATE (SELECT SALARY,
4 COMM,
4 AVG(SALARY) OVER (PARTITION BY WORKDEPT),
4 AVG(COMM) OVER (PARTITION BY WORKDEPT)
4 FROM EMPLOYEE)
4 AS E(SALARY, COMM, AVGSAL, AVGCOMM)
4 SET (SALARY, COMM)
4 = (AVGSAL, AVGCOMM)
4 WHERE EU.EMPNO = '000120'
上のステートメントは、意味的には次のステートメントと同等ですが、 4EMPLOYEE 表へのアクセスを一度しか必要としません。それに対し、次のステートメントでは、 4EMPLOYEE 表を二度指定します。
UPDATE EMPLOYEE EU
SET (EU.SALARY, EU.COMM)
=
(SELECT AVG(ES.SALARY), AVG(ES.COMM)
FROM EMPLOYEE ES
WHERE ES.WORKDEPT = EU.WORKDEPT)
WHERE EU.EMPNO = '000120'

また、Oracle における ROWNUM 相当の構文は、知っておきたいデータベース移行術「Oracle→DB2編」によると、こんな感じ。

Oracle
SELECT * FROM emp WHERE ROWNUM<11;
DB2
SELECT * FROM emp FETCH FIRST 10 ROWS ONLY;

SQL Server

TOP を使う。

SELECT TOP 1 uri FROM uniq_uri;

DISTINCT のような使い方をする。

MySQL

MySQL は、素直に LIMIT が UPDATE 文の中で使える。あの「他のＤＢ」とは、もしや MySQL だった*4のか？

MySQL バージョン 3.23 以降では、LIMIT row_count を使用して UPDATE のスコープを制限することができます。

PostgreSQL

FAQ を見る限りでは SELECT で LIMIT を使えるので、これを副問合せと組み合わせ UPDATE できるはず……だが、実際やるまで何とも言えない*5。

*1:ROWNUM は、結果セットに付加される連番を表す擬似列。引用の SQL では ORDER BY 及び ROWNUM 指定に対して各々１回ずつ副問合せを行っているが、これは、１度の問合せに両方のキーワードを使うと、結果セットに ROWNUM を振った後にORDER BY 指定によってソートされてしまうから（ROWNUM の順ではなくなるから）である。

*2:SELECT 文の FROM 句に来るとき、これをインラインビューと呼ぶ。

*3:冒頭で述べた日本メーカー製の RDBMS は、せっかく PL/SQL と類似の手続き型 SQL 拡張言語をもっているのに、ストアドプロシージャでしか手続き型ルーチンを書けない。

*4:Sybase という説もあるが、調べが及ばない。

*5:例によって、冒頭で述べた日本メーカー製の DBMS では、UPDATE 〜 SELECT 構文に「SELECT するテーブルと UPDATE するテーブルが同一であってはならない」という制限がある。