2
# test of updating of keys
6
drop table if exists t1,t2;
9
create table t1 (a int auto_increment , primary key (a));
10
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
11
update t1 set a=a+10 where a > 34;
12
update t1 set a=a+100 where a > 0;
14
# Some strange updates to test some otherwise unused code
15
update t1 set a=a+100 where a=1 and a=2;
17
update t1 set a=b+100 where a=1 and a=2;
19
update t1 set a=b+100 where c=1 and a=2;
21
update t1 set d=a+100 where a=1;
27
place_id int (10) unsigned NOT NULL,
28
shows int(10) unsigned DEFAULT '0' NOT NULL,
29
ishows int(10) unsigned DEFAULT '0' NOT NULL,
30
ushows int(10) unsigned DEFAULT '0' NOT NULL,
31
clicks int(10) unsigned DEFAULT '0' NOT NULL,
32
iclicks int(10) unsigned DEFAULT '0' NOT NULL,
33
uclicks int(10) unsigned DEFAULT '0' NOT NULL,
35
PRIMARY KEY (place_id,ts)
38
INSERT INTO t1 (place_id,shows,ishows,ushows,clicks,iclicks,uclicks,ts)
39
VALUES (1,0,0,0,0,0,0,20000928174434);
40
UPDATE t1 SET shows=shows+1,ishows=ishows+1,ushows=ushows+1,clicks=clicks+1,iclicks=iclicks+1,uclicks=uclicks+1 WHERE place_id=1 AND ts>="2000-09-28 00:00:00";
41
select place_id,shows from t1;
45
# Test bug with update reported by Jan Legenhausen
49
lfdnr int(10) unsigned NOT NULL default '0',
50
ticket int(10) unsigned NOT NULL default '0',
51
client varchar(255) NOT NULL default '',
52
replyto varchar(255) NOT NULL default '',
53
subject varchar(100) NOT NULL default '',
54
timestamp int(10) unsigned NOT NULL default '0',
55
tstamp timestamp NOT NULL,
56
status int(3) NOT NULL default '0',
57
type varchar(15) NOT NULL default '',
58
assignment int(10) unsigned NOT NULL default '0',
59
fupcount int(4) unsigned NOT NULL default '0',
60
parent int(10) unsigned NOT NULL default '0',
61
activity int(10) unsigned NOT NULL default '0',
62
priority tinyint(1) unsigned NOT NULL default '1',
63
cc varchar(255) NOT NULL default '',
64
bcc varchar(255) NOT NULL default '',
76
INSERT INTO t1 VALUES (773,773,'','','',980257344,20010318180652,0,'Open',10,0,0,0,1,'','','','','');
78
alter table t1 change lfdnr lfdnr int(10) unsigned not null auto_increment;
79
update t1 set status=1 where type='Open';
80
select status from t1;
87
create table t1 (a int not null, b int not null, key (a));
88
insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
90
update t1 set b=(@tmp:=@tmp+1) order by a;
91
update t1 set b=99 where a=1 order by b asc limit 1;
92
select * from t1 order by a,b;
93
update t1 set b=100 where a=1 order by b desc limit 2;
94
update t1 set a=a+10+b where a=1 order by b;
95
select * from t1 order by a,b;
96
create table t2 (a int not null, b int not null);
97
insert into t2 values (1,1),(1,2),(1,3);
98
update t1 set b=(select distinct 1 from (select * from t2) a);
102
# Test with limit (Bug #393)
106
`id_param` smallint(3) unsigned NOT NULL default '0',
107
`nom_option` char(40) NOT NULL default '',
108
`valid` tinyint(1) NOT NULL default '0',
109
KEY `id_param` (`id_param`,`nom_option`)
112
INSERT INTO t1 (id_param,nom_option,valid) VALUES (185,'600x1200',1);
114
UPDATE t1 SET nom_option='test' WHERE id_param=185 AND nom_option='600x1200' AND valid=1 LIMIT 1;
119
# Multi table update test from bugs
122
create table t1 (F1 VARCHAR(30), F2 VARCHAR(30), F3 VARCHAR(30), cnt int, groupid int, KEY groupid_index (groupid));
124
insert into t1 (F1,F2,F3,cnt,groupid) values ('0','0','0',1,6),
125
('0','1','2',1,5), ('0','2','0',1,3), ('1','0','1',1,2),
126
('1','2','1',1,1), ('1','2','2',1,1), ('2','0','1',2,4),
128
delete from m1 using t1 m1,t1 m2 where m1.groupid=m2.groupid and (m1.cnt < m2.cnt or m1.cnt=m2.cnt and m1.F3>m2.F3);
133
# Bug#5553 - Multi table UPDATE IGNORE fails on duplicate keys
137
`colA` int(10) unsigned NOT NULL auto_increment,
138
`colB` int(11) NOT NULL default '0',
141
INSERT INTO t1 VALUES (4433,5424);
143
`colC` int(10) unsigned NOT NULL default '0',
144
`colA` int(10) unsigned NOT NULL default '0',
145
`colD` int(10) unsigned NOT NULL default '0',
146
`colE` int(10) unsigned NOT NULL default '0',
147
`colF` int(10) unsigned NOT NULL default '0',
148
PRIMARY KEY (`colC`,`colA`,`colD`,`colE`)
150
INSERT INTO t2 VALUES (3,4433,10005,495,500);
151
INSERT INTO t2 VALUES (3,4433,10005,496,500);
152
INSERT INTO t2 VALUES (3,4433,10009,494,500);
153
INSERT INTO t2 VALUES (3,4433,10011,494,500);
154
INSERT INTO t2 VALUES (3,4433,10005,497,500);
155
INSERT INTO t2 VALUES (3,4433,10013,489,500);
156
INSERT INTO t2 VALUES (3,4433,10005,494,500);
157
INSERT INTO t2 VALUES (3,4433,10005,493,500);
158
INSERT INTO t2 VALUES (3,4433,10005,492,500);
159
UPDATE IGNORE t2,t1 set t2.colE = t2.colE + 1,colF=0 WHERE t1.colA = t2.colA AND (t1.colB & 4096) > 0 AND (colE + 1) < colF;
167
create table t1 (c1 int, c2 char(6), c3 int);
168
create table t2 (c1 int, c2 char(6));
169
insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
170
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
171
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
177
create table t1 (id int not null auto_increment primary key, id_str varchar(32));
178
insert into t1 (id_str) values ("test");
179
update t1 set id_str = concat(id_str, id) where id = last_insert_id();
184
# Bug #8942: a problem with update and partial key part
187
create table t1 (a int, b char(255), key(a, b(20)));
188
insert into t1 values (0, '1');
189
update t1 set b = b + 1 where a = 0;
193
# BUG#9103 "Erroneous data truncation warnings on multi-table updates"
194
create table t1 (a int, b varchar(10), key b(b(5))) engine=myisam;
195
create table t2 (a int, b varchar(10)) engine=myisam;
196
insert into t1 values ( 1, 'abcd1e');
197
insert into t1 values ( 2, 'abcd2e');
198
insert into t2 values ( 1, 'abcd1e');
199
insert into t2 values ( 2, 'abcd2e');
201
update t1, t2 set t1.a = t2.a where t2.b = t1.b;
206
# Bug #11868 Update with subquery with ref built with a key from the updated
207
# table crashes server
209
create table t1(f1 int, f2 int);
210
create table t2(f3 int, f4 int);
211
create index idx on t2(f3);
212
insert into t1 values(1,0),(2,0);
213
insert into t2 values(1,1),(2,2);
214
UPDATE t1 SET t1.f2=(SELECT MAX(t2.f4) FROM t2 WHERE t2.f3=t1.f1);
219
# Bug #13180 sometimes server accepts sum func in update/delete where condition
221
create table t1(f1 int);
224
update t1 set f1=1 where count(*)=1;
227
delete from t1 where count(*)=1;
230
# BUG#12915: Optimize "DELETE|UPDATE ... ORDER BY ... LIMIT n" to use an index
231
create table t1 ( a int, b int default 0, index (a) );
232
insert into t1 (a) values (0),(0),(0),(0),(0),(0),(0),(0);
235
select a from t1 order by a limit 1;
236
show status like 'handler_read%';
239
update t1 set a=9999 order by a limit 1;
240
analyze table t1; # PBXT required to get a consistent order in the update below
241
update t1 set b=9999 order by a desc limit 1; # PBXT Update last updated field
242
show status like 'handler_read%';
245
delete from t1 order by a limit 1;
246
show status like 'handler_read%';
249
delete from t1 order by a desc limit 1;
250
show status like 'handler_read%';
252
alter table t1 disable keys;
255
delete from t1 order by a limit 1;
256
show status like 'handler_read%';
259
update t1 set a=a+10,b=1 order by a limit 3;
260
update t1 set a=a+11,b=2 order by a limit 3;
261
update t1 set a=a+12,b=3 order by a limit 3;
262
select * from t1 order by a;
267
# Bug#14186 select datefield is null not updated
269
create table t1 (f1 date not null);
270
insert into t1 values('2000-01-01'),('0000-00-00');
271
update t1 set f1='2002-02-02' where f1 is null;
276
# Bug#15028 Multitable update returns different numbers of matched rows
277
# depending on table order
278
create table t1 (f1 int);
279
create table t2 (f2 int);
280
insert into t1 values(1),(2);
281
insert into t2 values(1),(1);
283
update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
286
update t1 set f1=1 where f1=3;
288
update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
294
create table t1 (a int);
295
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
296
create table t2 (a int, filler1 char(200), filler2 char(200), key(a));
297
insert into t2 select A.a + 10*B.a, 'filler','filler' from t1 A, t1 B;
299
update t2 set a=3 where a=2;
300
show status like 'handler_read%';
304
# Bug #16510 Updating field named like '*name' caused server crash
306
create table t1(f1 int, `*f2` int);
307
insert into t1 values (1,1);
308
update t1 set `*f2`=1;