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);
135
create table t1 (c1 int, c2 char(6), c3 int);
136
create table t2 (c1 int, c2 char(6));
137
insert into t1 values (1, "t1c2-1", 10), (2, "t1c2-2", 20);
138
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1";
139
update t1 left join t2 on t1.c1 = t2.c1 set t2.c2 = "t2c2-1" where t1.c3 = 10;
145
121
create table t1 (id int not null auto_increment primary key, id_str varchar(32));
158
134
select * from t1;
161
# BUG#9103 "Erroneous data truncation warnings on multi-table updates"
162
create temporary table t1 (a int, b varchar(10), key b(b(5))) engine=myisam;
163
create temporary table t2 (a int, b varchar(10)) engine=myisam;
164
insert into t1 values ( 1, 'abcd1e');
165
insert into t1 values ( 2, 'abcd2e');
166
insert into t2 values ( 1, 'abcd1e');
167
insert into t2 values ( 2, 'abcd2e');
169
update t1, t2 set t1.a = t2.a where t2.b = t1.b;
174
138
# Bug #11868 Update with subquery with ref built with a key from the updated
175
139
# table crashes server
241
205
select * from t1;
245
# Bug#15028 Multitable update returns different numbers of matched rows
246
# depending on table order
247
create table t1 (f1 int);
248
create table t2 (f2 int);
249
insert into t1 values(1),(2);
250
insert into t2 values(1),(1);
252
update t1,t2 set f1=3,f2=3 where f1=f2 and f1=1;
255
update t1 set f1=1 where f1=3;
257
update t2,t1 set f1=3,f2=3 where f1=f2 and f1=1;
263
209
create table t1 (a int);
264
210
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
372
318
# Check that the number of matched rows is correct when the temporary
373
319
# table is small enough to not be converted to MyISAM
374
320
select count(*) from t1 join t2 on (t1.a=t2.a);
376
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
379
322
# Increase table sizes
380
323
insert into t2(a) select a from t2;
385
328
# Check that the number of matched rows is correct when the temporary
386
329
# table has to be converted to MyISAM
387
330
select count(*) from t1 join t2 on (t1.a=t2.a);
389
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
392
332
# Check that the number of matched rows is correct when there are duplicate
394
334
update t1 set a=1;
395
335
update t2 set a=1;
396
336
select count(*) from t1 join t2 on (t1.a=t2.a);
398
update t1 join t2 on (t1.a=t2.a) set t1.id=t2.id;
401
338
drop table t1,t2;
added
removed
tests/t/update.test
