DELETE

2003-12-23T21:00+09:00 matsu

DBのレコードの削除にはDELETEを使用する.

基本形
サブクエリ

DELETEの基本形は以下である.

DELETE FROM テーブル名 WHERE...

指定したテーブルからWHERE句の条件を満たすレコードを削除する.

benchmark=> SELECT * FROM result_netpipe 
benchmark-> WHERE size < 10 AND type='tcp';
 type | size | cluster_id |   mbps   | exec_date  
------+------+------------+----------+------------
 tcp  |    1 |          2 | 0.161336 | 2003-05-03
 tcp  |    1 |          3 | 0.127642 | 2003-11-03
 tcp  |    2 |          2 | 0.320775 | 2003-05-03
 tcp  |    2 |          3 | 0.239936 | 2003-11-03
 tcp  |    3 |          2 |  0.50189 | 2003-05-03
 tcp  |    3 |          3 | 0.390634 | 2003-11-03
 tcp  |    4 |          2 | 0.664022 | 2003-05-03
 tcp  |    4 |          3 | 0.521234 | 2003-11-03
 tcp  |    6 |          2 | 0.959799 | 2003-05-03
 tcp  |    6 |          3 | 0.703179 | 2003-11-03
 tcp  |    8 |          2 | 1.321577 | 2003-05-03
 tcp  |    8 |          3 | 1.026098 | 2003-11-03
(12 rows)

benchmark=> DELETE FROM result_netpipe 
benchmark-> WHERE size < 10 AND type='tcp';
DELETE 12

benchmark=> SELECT * FROM result_netpipe 
benchmark-> WHERE size < 10 AND type='tcp';
 type | size | cluster_id | mbps | exec_date 
------+------+------------+------+-----------
(0 rows)

サブクエリ

SELECT,UPDATEと同様,WHERE句でサブクエリを使用できる.

benchmark=> SELECT min(mbps) FROM result_netpipe;
   min    
----------
 0.085287
(1 row)

benchmark=> DELETE FROM result_netpipe 
benchmark-> WHERE mbps=(SELECT min(mbps) FROM result_netpipe);
DELETE 1

benchmark=> SELECT min(mbps) FROM result_netpipe;
   min    
----------
 0.111561
(1 row)