oceanbase

1
drop table if exists t1;
2
create table t1 (a int, b int, primary key (a));
3
insert into t1(a,b) values(1,1),(2,1),(3,1),(4,1);
4
update t1 set b=2 where a>0;
5
select * from t1;
6
a	b
7
1	2
8
2	2
9
3	2
10
4	2
11
update t1 set b=3 where a<5;
12
select * from t1;
13
a	b
14
1	3
15
2	3
16
3	3
17
4	3
18
update t1 set b=4 where a>1;
19
update t1 set b=5 where a<4;
20
select * from t1;
21
a	b
22
1	5
23
2	5
24
3	5
25
4	4
26
update t1 set b=6 where a>=1;
27
select * from t1;
28
a	b
29
1	6
30
2	6
31
3	6
32
4	6
33
update t1 set b=7 where a<=5;
34
select * from t1;
35
a	b
36
1	7
37
2	7
38
3	7
39
4	7
40
update t1 set b=8 where a>0 and a<2;
41
update t1 set b=9 where a>=2 and a<3;
42
update t1 set b=10 where a>=3 and a<=4;
43
select * from t1;
44
a	b
45
1	8
46
2	9
47
3	10
48
4	10
49
update t1 set b=11 where a=1 or a=2 or a=3 or a=4;
50
select * from t1;
51
a	b
52
1	11
53
2	11
54
3	11
55
4	11
56
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
57
update t1 set b=b+1 where b>0;
58
select * from t1;
59
a	b
60
1	2
61
2	3
62
3	4
63
4	5
64
update t1 set b=b-1 where b<6;
65
select * from t1;
66
a	b
67
1	1
68
2	2
69
3	3
70
4	4
71
update t1 set b=5 where b>1;
72
update t1 set b=6 where b<5;
73
select * from t1;
74
a	b
75
1	6
76
2	5
77
3	5
78
4	5
79
update t1 set b=7 where b>=1;
80
select * from t1;
81
a	b
82
1	7
83
2	7
84
3	7
85
4	7
86
update t1 set b=8 where b<=7;
87
select * from t1;
88
a	b
89
1	8
90
2	8
91
3	8
92
4	8
93
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
94
update t1 set b=10 where b>0 and b<2;
95
update t1 set b=11 where b>=2 and b<3;
96
update t1 set b=12 where b>=3 and b<=4;
97
select * from t1;
98
a	b
99
1	10
100
2	11
101
3	12
102
4	12
103
replace into t1(a,b) values(1,1),(2,2),(3,3),(4,4);
104
update t1 set b=11 where b=1 or b=2 or b=3 or b=4;
105
select * from t1;
106
a	b
107
1	11
108
2	11
109
3	11
110
4	11
111
drop table if exists t1;
112
create table t1(a int, b int, c int, primary key(a));
113
replace into t1(a,b) values(1,1),(2,2),(3,1),(4,2);
114
update t1 set c=1 where a>0 and b>1;
115
update t1 set c=2 where a<5 and b<2;
116
select * from t1;
117
a	b	c
118
1	1	2
119
2	2	1
120
3	1	2
121
4	2	1
122
update t1 set c=3 where a>=0 and b<=1;
123
update t1 set c=4 where a<=5 and b>=2;
124
select * from t1;
125
a	b	c
126
1	1	3
127
2	2	4
128
3	1	3
129
4	2	4
130
update t1 set c=5 where a=0 or a=1 or a=2 or b=1;
131
update t1 set c=6 where b=2 or a=1 or a=2;
132
select * from t1;
133
a	b	c
134
1	1	6
135
2	2	6
136
3	1	5
137
4	2	6
138
replace into t1(a,b,c) values(1,1,NULL),(2,2,NULL),(3,3,NULL),(4,4,NULL);
139
update t1 set c=1 where a<=1 or b>=4;
140
update t1 set c=2 where a>1 or b<4;
141
select * from t1;
142
a	b	c
143
1	1	2
144
2	2	2
145
3	3	2
146
4	4	2
147
drop table if exists t1;
148
create table t1(a int, b int, c int, d int, primary key(a,b));
149
insert into t1(a,b,c,d) values(1,1,1,1),(2,2,1,1),(3,3,1,1);
150
update t1 set c=c+1, d=d+1 where (a,b) > (0,0);
151
select * from t1;
152
a	b	c	d
153
1	1	2	2
154
2	2	2	2
155
3	3	2	2
156
update t1 set c=c+1, d=d+1 where (a,b) < (4,4);
157
select * from t1;
158
a	b	c	d
159
1	1	3	3
160
2	2	3	3
161
3	3	3	3
162
update t1 set c=c+1, d=d+1 where (a,b) >= (1,1);
163
select * from t1;
164
a	b	c	d
165
1	1	4	4
166
2	2	4	4
167
3	3	4	4
168
update t1 set c=c+1, d=d+1 where (a,b) <= (1,1);
169
select * from t1;
170
a	b	c	d
171
1	1	5	5
172
2	2	4	4
173
3	3	4	4
174
update t1 set c=c+1, d=d+1 where (a,b) in ((NULL,NULL),(0,0),(1,1),(2,2),(3,3),(4,4));
175
select * from t1;
176
a	b	c	d
177
1	1	6	6
178
2	2	5	5
179
3	3	5	5
180
update t1 set c=c+1, d=d+1 where (a,b)=(1,1) or (a,b)=(2,2) or (a,b)=(3,3);
181
select * from t1;
182
a	b	c	d
183
1	1	7	7
184
2	2	6	6
185
3	3	6	6
186
update t1 set c=1, d=1 where (a,b) in ((1,1),(2,2),(3,3));
187
update t1 set c=c+1, d=d+1 where (a,b,c,d) >= (1,1,1,1);
188
select * from t1;
189
a	b	c	d
190
1	1	2	2
191
2	2	2	2
192
3	3	2	2
193
update t1 set c=c+1, d=d+1 where (a,b,c,d) <= (3,3,3,3);
194
select * from t1;
195
a	b	c	d
196
1	1	3	3
197
2	2	3	3
198
3	3	3	3
199
update t1 set c=c+1, d=d+1 where (a,b,c,d)=(1,1,1,1) or (a,b,c,d)=(2,2,1,1) or (a,b,c,d)=(3,3,3,3);
200
select * from t1;
201
a	b	c	d
202
1	1	3	3
203
2	2	3	3
204
3	3	4	4
205
drop table if exists t1;
206
create table t1(a int, b int, c int, d int, primary key(a,b));
207
insert into t1(a,b,c,d) values(1,1,1,1),(1,2,1,1),(1,3,1,1);
208
update t1 set c=c+1, d=d+1 where a = 1;
209
select * from t1;
210
a	b	c	d
211
1	1	2	2
212
1	2	2	2
213
1	3	2	2
214
update t1 set c=c+1, d=d+1 where a > 0;
215
update t1 set c=c+1, d=d+1 where a >=1 ;
216
select * from t1;
217
a	b	c	d
218
1	1	4	4
219
1	2	4	4
220
1	3	4	4
221
update t1 set c=c+1, d=d+1 where a < 4;
222
select * from t1;
223
a	b	c	d
224
1	1	5	5
225
1	2	5	5
226
1	3	5	5
227
update t1 set c=c+1, d=d+1 where a <= 1;
228
select * from t1;
229
a	b	c	d
230
1	1	6	6
231
1	2	6	6
232
1	3	6	6
233
update t1 set c=c+1, d=d+1 where a=1 or a=2 or a=3;
234
select * from t1;
235
a	b	c	d
236
1	1	7	7
237
1	2	7	7
238
1	3	7	7
239
update t1 set c=c+1, d=d+1 where (a=1 or a=2 or a=3) and (b=1 or b=2);
240
select * from t1;
241
a	b	c	d
242
1	1	8	8
243
1	2	8	8
244
1	3	7	7
245
update t1 set c=c+1, d=d+1 where a>=1 and b>=2;
246
select * from t1;
247
a	b	c	d
248
1	1	8	8
249
1	2	9	9
250
1	3	8	8
251
drop table if exists t1;
252
create table t1(a int, b int, c int, d int, primary key(a,b));
253
replace into t1(a,b,c,d) values(1,1,1,1),(2,2,2,1),(3,3,3,1);
254
update t1 set d=d+1 where (a,b)>=(1,1) and c=1;
255
select * from t1;
256
a	b	c	d
257
1	1	1	2
258
2	2	2	1
259
3	3	3	1
260
update t1 set d=d+1 where (a,b)>=(1,1) and c=3;
261
select * from t1;
262
a	b	c	d
263
1	1	1	2
264
2	2	2	1
265
3	3	3	2
266
update t1 set d=d+1 where (a=1 or a=2 or a=3) and c>=1;
267
select * from t1;
268
a	b	c	d
269
1	1	1	3
270
2	2	2	2
271
3	3	3	3
272
update t1 set d=d+1 where (a=1 or a=2 or a=3) and c<1;
273
select * from t1;
274
a	b	c	d
275
1	1	1	3
276
2	2	2	2
277
3	3	3	3
278
update t1 set d=d+1 where (a=1 or a=2 or a=3) and (b=2 or b=3);
279
select * from t1;
280
a	b	c	d
281
1	1	1	3
282
2	2	2	3
283
3	3	3	4
284
update t1 set d=d+1 where (a=1 or a=2 or a=3) and (b=2 or b=3) and c=3;
285
select * from t1;
286
a	b	c	d
287
1	1	1	3
288
2	2	2	3
289
3	3	3	5
290
update t1 set c=c+1, d=d+1 where a>=0 and a<=3 and b>1 and b<3;
291
select * from t1;
292
a	b	c	d
293
1	1	1	3
294
2	2	3	4
295
3	3	3	5
296
update t1 set c=c+1, d=d+1 where (c,d)>=(1,1);
297
select * from t1;
298
a	b	c	d
299
1	1	2	4
300
2	2	4	5
301
3	3	4	6
302
update t1 set c=c+1, d=d+1 where (b,a) > (1,1);
303
select * from t1;
304
a	b	c	d
305
1	1	2	4
306
2	2	5	6
307
3	3	5	7
308
drop table if exists t1, t2, t3;
309
create table t1(a int, b int, c int, d int, primary key(a,b,c));
310
insert into t1(a,b,c) values (1,1,1),(2,2,2),(3,3,3);
311
update t1 set d=1 where (a,b,c)>(0,0,0) and (a,b,c)<=(3,3,3);
312
select * from t1;
313
a	b	c	d
314
1	1	1	1
315
2	2	2	1
316
3	3	3	1
317
create table t2(a varchar(1024), b varchar(1024), c varchar(1024), d varchar(1024), primary key(a,b,c));
318
insert into t2(a,b,c) values ('a','a','a'),('b','b','b'),('c','c','c');
319
update t2 set d='a' where (a,b,c)>=('a','a','b') and (a,b,c)<('b','b','c');
320
select * from t2;
321
a	b	c	d
322
a	a	a	NULL
323
b	b	b	a
324
c	c	c	NULL
325
create table t3(a int, b varchar(1024), c  timestamp(6) default "2012-01-01 12:00:00", d int, primary key(a,b,c));
326
insert into t3(a,b,c) values (1,'a','2014-02-17'),(2,'b','2014-02-17'),(3,'c','2014-02-18');
327
update t3 set d=1 where (a,b,c)>=(1,'a','2014-02-17 00:00:00') and (a,b,c)<=(2,'b','2014-02-18');
328
select * from t3;
329
a	b	c	d
330
1	a	2014-02-17 00:00:00.000000	1
331
2	b	2014-02-17 00:00:00.000000	1
332
3	c	2014-02-18 00:00:00.000000	NULL
333
drop table if exists t1, t2, t3;
334