使用MySQL的用户可能会比较熟悉这样的用法,更新或删除时可以指定限制更新或删除多少条记录。
update tl set xxx=xxx where xxx limit 10;
delete from tl where xxx limit 10;
目前KingbaseES没有类似的语法,但是可以通过子查询来达到同样的效果。
创建测试表
test=# create table t1(id number, uid number);
CREATE TABLE
test=# insert into t1 SELECT generate_series(1,20000),(random()*(10^7))::integer;
INSERT 0 20000
update | delete limit 子查询写法
test=# DELETE FROM t1 WHERE id in(SELECT id FROM t1 ORDER BY id limit 10);
DELETE 10
test=# update t1 set uid=999 where id in (select id from t1 where id between 50 and 200 limit 30);
UPDATE 30
test=# select * from t1 where id between 50 and 200;
id | uid
-----+---------
61 | 999
62 | 999
63 | 999
64 | 999
65 | 999
66 | 999
67 | 999
68 | 999
69 | 999
70 | 999
71 | 999
72 | 999
73 | 999
74 | 999
75 | 999
76 | 999
77 | 999
78 | 999
79 | 999
80 | 999
81 | 999
82 | 999
83 | 999
84 | 999
85 | 999
86 | 999
87 | 999
88 | 999
89 | 999
90 | 999
91 | 4791280
92 | 8658152
93 | 4275799
94 | 1188468
95 | 4405454
96 | 6355264
97 | 3400361
98 | 7912872
99 | 4159834
100 | 8621857
标签:10,where,999,t1,limit,MYSQL,KingbaseES,id
From: https://www.cnblogs.com/kingbase/p/16945447.html