如何删除表中的前n条记录

frame2006

我的DB2是V7，好像不支持row_number() over()这个函数，哪位高人能不吝赐教，感激！

bpmfhu

给这个表建一个sequence,然后删除前几行的记录

frame2006

这样恐怕不行，假如有5条记录，序号是1、2、3、4、5，由于一些原因，第1条被删除了；此时，我想删除该表的前2条记录，则只有序号为2的那条会被删除，而实际上我是想删除序号为2、3的两条。

beginner_bj

先用row_number() over()或fetch first n rows only生成一个临时表

然后问题转化为：删除本表中与另一个表中所有相同记录的问题

beginner_bj

刚刚试了以下V7好象不支持delete from ... where in ...，
只好用脚本了。
db2 => select * from t2;

C1          C2                        
----------- --------------------------
          1 2006-06-15-13.56.10.335031
          2 2006-06-15-13.56.16.688906
          3 2006-06-15-13.56.18.091722
          4 2006-06-15-13.56.19.215765
          5 2006-06-15-13.56.20.180413
          6 2006-06-15-13.56.21.040426
          7 2006-06-15-13.56.22.372002

  7 record(s) selected.

db2 => quit;
DB20000I  The QUIT command completed successfully.
$ db2 "delete from t2 b where b.c1 in (select a.c1 from t2 a fetch first 1 rows only)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "delete from t2 b where b.c1 in (select a.c" was
found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  
"<values>".  SQLSTATE=42601
$ db2 "delete from t2 b where b.c1 = (select a.c1 from t2 a fetch first 1 rows only)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "delete from t2 b where b.c1 = (select a.c1" was
found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  
"<values>".  SQLSTATE=42601
$ db2 "select a.c1 from t2 a fetch first 1 rows only"

C1         
-----------
          1

  1 record(s) selected.

$ db2 -x select a.c1 from t2 a fetch first 2 rows only
          1
          2
$ for i in `db2 -x select a.c1 from t2 a fetch first 2 rows only`; do db2 "delete from t2 b where b.c1 = $i"; done
DB20000I  The SQL command completed successfully.
DB20000I  The SQL command completed successfully.
$ db2 "select * from t2"

C1          C2                        
----------- --------------------------
          3 2006-06-15-13.56.18.091722
          4 2006-06-15-13.56.19.215765
          5 2006-06-15-13.56.20.180413
          6 2006-06-15-13.56.21.040426
          7 2006-06-15-13.56.22.372002

  5 record(s) selected.


$ db2level
DB21085I  Instance "ldapdb2" uses DB2 code release "SQL07020" with level
identifier "03010105" and informational tokens "DB2 v7.1.0.40", "s010415" and
"U475375".

$

xincheng24

CREATE TABLE SAMSON_ZHU.T1
(C1  INTEGER,
  C2  INTEGER
)
-----------------------------------------------------
INSERT INTO T1 VALUES(100,123),
(101,123),
(102,123),
(103,123),
(104,123),
(105,123),
(106,123)
-------------------------------------

DELETE FROM T1 WHERE C1 IN (SELECT C1 FROM T1 FETCH FIRST 3 ROWS ONLY)

THAT IS OK ~~~

ENJOY IT


---------------------------------
xincheng24@hotmail.com
infosys

midzhou

可以直接使用delete from (select * from T1 where ... fetch first 3 rows only) where 1=1
不过该语法只要DB2 V8.2以上有效