1
drop table if exists t1,t2,t3;
2
drop database if exists mysqltest;
3
drop view if exists v1;
4
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
5
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
6
delete from mysql.user where user=_binary'mysqltest_1';
7
create table t1(id1 int not null auto_increment primary key, t char(12));
8
create table t2(id2 int not null, t char(12));
9
create table t3(id3 int not null, t char(12), index(id3));
10
select count(*) from t1 where id1 > 95;
13
select count(*) from t2 where id2 > 95;
16
select count(*) from t3 where id3 > 95;
19
update t1,t2,t3 set t1.t="aaa", t2.t="bbb", t3.t="cc" where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 90;
20
select count(*) from t1 where t = "aaa";
23
select count(*) from t1 where id1 > 90;
26
select count(*) from t2 where t = "bbb";
29
select count(*) from t2 where id2 > 90;
32
select count(*) from t3 where t = "cc";
35
select count(*) from t3 where id3 > 90;
38
delete t1.*, t2.*, t3.* from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 95;
39
check table t1, t2, t3;
40
Table Op Msg_type Msg_text
41
test.t1 check status OK
42
test.t2 check status OK
43
test.t3 check status OK
44
select count(*) from t1 where id1 > 95;
47
select count(*) from t2 where id2 > 95;
50
select count(*) from t3 where id3 > 95;
53
delete t1, t2, t3 from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 5;
54
select count(*) from t1 where id1 > 5;
57
select count(*) from t2 where id2 > 5;
60
select count(*) from t3 where id3 > 5;
63
delete from t1, t2, t3 using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3 and t1.id1 > 0;
64
select count(*) from t1 where id1;
67
select count(*) from t2 where id2;
70
select count(*) from t3 where id3;
74
create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1;
75
create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;
76
delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
79
id int(11) NOT NULL default '0',
80
name varchar(10) default NULL,
83
INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
85
id int(11) NOT NULL default '0',
86
name varchar(10) default NULL,
89
INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
91
id int(11) NOT NULL default '0',
92
mydate datetime default NULL,
95
INSERT INTO t3 VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22
96
00:00:00'),(7,'2002-07-22 00:00:00');
97
delete t1,t2,t3 from t1,t2,t3 where to_days(now())-to_days(t3.mydate)>=30 and t3.id=t1.id and t3.id=t2.id;
100
1 2002-02-04 00:00:00
101
5 2002-05-12 00:00:00
102
6 2002-06-22 00:00:00
103
7 2002-07-22 00:00:00
105
CREATE TABLE IF NOT EXISTS `t1` (
106
`id` int(11) NOT NULL auto_increment,
111
CREATE TABLE IF NOT EXISTS `t2` (
112
`ID` int(11) NOT NULL auto_increment,
113
`ParId` int(11) default NULL,
117
KEY `IX_ParId_t2` (`ParId`),
118
FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)
120
INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
121
INSERT INTO t2(ParId) VALUES(1), (2), (3);
127
UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;
134
create table t1 (n numeric(10));
135
create table t2 (n numeric(10));
136
insert into t2 values (1),(2),(4),(8),(16),(32);
137
select * from t2 left outer join t1 using (n);
145
delete t1,t2 from t2 left outer join t1 using (n);
146
select * from t2 left outer join t1 using (n);
149
create table t1 (n int(10) not null primary key, d int(10));
150
create table t2 (n int(10) not null primary key, d int(10));
151
insert into t1 values(1,1);
152
insert into t2 values(1,10),(2,20);
153
LOCK TABLES t1 write, t2 read;
154
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
155
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
156
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
157
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
158
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
160
LOCK TABLES t1 write, t2 write;
161
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
165
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
173
set sql_safe_updates=1;
174
create table t1 (n int(10), d int(10));
175
create table t2 (n int(10), d int(10));
176
insert into t1 values(1,1);
177
insert into t2 values(1,10),(2,20);
178
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
179
ERROR HY000: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
180
set sql_safe_updates=0;
182
set timestamp=1038401397;
183
create table t1 (n int(10) not null primary key, d int(10), t timestamp);
184
create table t2 (n int(10) not null primary key, d int(10), t timestamp);
185
insert into t1 values(1,1,NULL);
186
insert into t2 values(1,10,NULL),(2,20,NULL);
187
set timestamp=1038000000;
188
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
189
select n,d,unix_timestamp(t) from t1;
190
n d unix_timestamp(t)
192
select n,d,unix_timestamp(t) from t2;
193
n d unix_timestamp(t)
196
UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
197
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1=2 WHERE t1.n=t2.n' at line 1
200
set sql_safe_updates=0;
201
create table t1 (n int(10) not null primary key, d int(10));
202
create table t2 (n int(10) not null primary key, d int(10));
203
insert into t1 values(1,1), (3,3);
204
insert into t2 values(1,10),(2,20);
205
UPDATE t2 left outer join t1 on t1.n=t2.n SET t1.d=t2.d;
215
create table t1 (n int(10), d int(10));
216
create table t2 (n int(10), d int(10));
217
insert into t1 values(1,1),(1,2);
218
insert into t2 values(1,10),(2,20);
219
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
229
create table t1 (n int(10), d int(10));
230
create table t2 (n int(10), d int(10));
231
insert into t1 values(1,1),(3,2);
232
insert into t2 values(1,10),(1,20);
233
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
242
UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
251
DELETE a, b FROM t1 a,t2 b where a.n=b.n;
258
CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;
259
INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,'');
260
CREATE TABLE t2 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;
261
INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a');
262
CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0', naziv char(25) NOT NULL default 'NEPOZNAT', PRIMARY KEY (broj)) ENGINE=MyISAM;
263
INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');
264
update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;
265
update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;
267
CREATE TABLE t1 (a int not null primary key, b int not null, key (b));
268
CREATE TABLE t2 (a int not null primary key, b int not null, key (b));
269
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
270
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
271
update t1,t2 set t1.a=t1.a+100;
283
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
295
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
307
update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t2.a=t1.a-100;
330
update t1,t2 set t1.b=t2.b, t1.a=t2.a where t1.a=t2.a and not exists (select * from t2 where t2.a > 10);
332
CREATE TABLE t3 ( KEY1 varchar(50) NOT NULL default '', PARAM_CORR_DISTANCE_RUSH double default NULL, PARAM_CORR_DISTANCE_GEM double default NULL, PARAM_AVG_TARE double default NULL, PARAM_AVG_NB_DAYS double default NULL, PARAM_DEFAULT_PROP_GEM_SRVC varchar(50) default NULL, PARAM_DEFAULT_PROP_GEM_NO_ETIK varchar(50) default NULL, PARAM_SCENARIO_COSTS varchar(50) default NULL, PARAM_DEFAULT_WAGON_COST double default NULL, tmp int(11) default NULL, PRIMARY KEY (KEY1)) ENGINE=MyISAM;
333
INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL);
334
create table t1 (A varchar(1));
335
insert into t1 values ("A") ,("B"),("C"),("D");
336
create table t2(Z varchar(15));
337
insert into t2(Z) select concat(a.a,b.a,c.a,d.a) from t1 as a, t1 as b, t1 as c, t1 as d;
338
update t2,t3 set Z =param_scenario_costs;
340
create table t1 (a int, b int);
341
create table t2 (a int, b int);
342
insert into t1 values (1,1),(2,1),(3,1);
343
insert into t2 values (1,1), (3,1);
344
update t1 left join t2 on t1.a=t2.a set t1.b=2, t2.b=2 where t1.b=1 and t2.b=1 or t2.a is NULL;
345
select t1.a, t1.b,t2.a, t2.b from t1 left join t2 on t1.a=t2.a where t1.b=1 and t2.b=1 or t2.a is NULL;
349
create table t1 (a int not null auto_increment primary key, b int not null);
350
insert into t1 (b) values (1),(2),(3),(4);
351
update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a;
359
create table t1(id1 smallint(5), field char(5));
360
create table t2(id2 smallint(5), field char(5));
361
insert into t1 values (1, 'a'), (2, 'aa');
362
insert into t2 values (1, 'b'), (2, 'bb');
371
update t2 inner join t1 on t1.id1=t2.id2
372
set t2.field=t1.field
374
update t2, t1 set t2.field=t1.field
375
where t1.id1=t2.id2 and 0=1;
376
delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2
378
delete t1, t2 from t2,t1
379
where t1.id1=t2.id2 and 0=1;
381
create table t1 ( a int not null, b int not null) ;
382
alter table t1 add index i1(a);
383
delete from t1 where a > 2000000;
384
create table t2 like t1;
385
insert into t2 select * from t1;
386
select 't2 rows before small delete', count(*) from t1;
387
t2 rows before small delete count(*)
388
t2 rows before small delete 2000000
389
delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 2;
390
select 't2 rows after small delete', count(*) from t2;
391
t2 rows after small delete count(*)
392
t2 rows after small delete 1999999
393
select 't1 rows after small delete', count(*) from t1;
394
t1 rows after small delete count(*)
395
t1 rows after small delete 1999999
396
delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000;
397
select 't2 rows after big delete', count(*) from t2;
398
t2 rows after big delete count(*)
399
t2 rows after big delete 1900001
400
select 't1 rows after big delete', count(*) from t1;
401
t1 rows after big delete count(*)
402
t1 rows after big delete 1900001
404
CREATE TABLE t1 ( a int );
405
CREATE TABLE t2 ( a int );
406
DELETE t1 FROM t1, t2 AS t3;
407
DELETE t4 FROM t1, t1 AS t4;
408
DELETE t3 FROM t1 AS t3, t1 AS t4;
409
DELETE t1 FROM t1 AS t3, t2 AS t4;
410
ERROR 42S02: Unknown table 't1' in MULTI DELETE
411
INSERT INTO t1 values (1),(2);
412
INSERT INTO t2 values (1),(2);
413
DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1;
421
DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2;
429
create table `t1` (`p_id` int(10) unsigned NOT NULL auto_increment, `p_code` varchar(20) NOT NULL default '', `p_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`p_id`) );
430
create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(10) unsigned NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) );
431
insert into t1 values (0,'A01-Comp',1);
432
insert into t1 values (0,'B01-Comp',1);
433
insert into t2 values (0,1,'A Note',1);
434
update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2;
440
c2_id c2_p_id c2_note c2_active
443
create database mysqltest;
444
create table mysqltest.t1 (a int, b int, primary key (a));
445
create table mysqltest.t2 (a int, b int, primary key (a));
446
create table mysqltest.t3 (a int, b int, primary key (a));
447
grant select on mysqltest.* to mysqltest_1@localhost;
448
grant update on mysqltest.t1 to mysqltest_1@localhost;
449
update t1, t2 set t1.b=1 where t1.a=t2.a;
450
update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a;
451
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
452
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
453
delete from mysql.user where user=_binary'mysqltest_1';
454
drop database mysqltest;
455
create table t1 (a int, primary key (a));
456
create table t2 (a int, primary key (a));
457
create table t3 (a int, primary key (a));
458
delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a);
459
ERROR 42S02: Unknown table 't3' in MULTI DELETE
460
drop table t1, t2, t3;
461
create table t1 (col1 int);
462
create table t2 (col1 int);
463
update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
464
ERROR HY000: You can't specify target table 't1' for update in FROM clause
465
delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
466
ERROR HY000: You can't specify target table 't1' for update in FROM clause
469
aclid bigint not null primary key,
470
status tinyint(1) not null
473
refid bigint not null primary key,
474
aclid bigint, index idx_acl(aclid)
476
insert into t2 values(1,null);
477
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
479
create table t1(a int);
480
create table t2(a int);
481
delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
482
ERROR HY000: You can't specify target table 't1' for update in FROM clause
484
create table t1 ( c char(8) not null ) engine=innodb;
485
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
486
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
487
alter table t1 add b char(8) not null;
488
alter table t1 add a char(8) not null;
489
alter table t1 add primary key (a,b,c);
490
update t1 set a=c, b=c;
491
create table t2 like t1;
492
insert into t2 select * from t1;
493
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
495
create table t1 ( c char(8) not null ) engine=innodb;
496
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
497
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
498
alter table t1 add b char(8) not null;
499
alter table t1 add a char(8) not null;
500
alter table t1 add primary key (a,b,c);
501
update t1 set a=c, b=c;
502
create table t2 like t1;
503
insert into t2 select * from t1;
504
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
506
create table t1 (a int, b int);
507
insert into t1 values (1, 2), (2, 3), (3, 4);
508
create table t2 (a int);
509
insert into t2 values (10), (20), (30);
510
create view v1 as select a as b, a/10 as a from t2;
512
alter table t1 add column c int default 100 after a;
513
update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a;
527
create table t1 (i1 int, i2 int, i3 int);
528
create table t2 (id int, c1 varchar(20), c2 varchar(20));
529
insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
530
insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
531
select * from t1 order by i1;
543
update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
544
select * from t1 order by i1;
551
select * from t2 order by id;
556
delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
557
select * from t1 order by i1;
562
select * from t2 order by id;
567
create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1));
568
create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id));
569
insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
570
insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
571
select * from t1 order by i1;
578
select * from t2 order by id;
583
update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
584
select * from t1 order by i1;
591
select * from t2 order by id;
596
delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
597
select * from t1 order by i1;
602
select * from t2 order by id;
608
`a` int(11) NOT NULL auto_increment,
609
`b` int(11) default NULL,
611
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
613
`a` int(11) NOT NULL auto_increment,
614
`b` int(11) default NULL,
616
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
617
set @sav_binlog_format= @@session.binlog_format;
618
set @@session.binlog_format= mixed;
619
insert into t1 values (1,1),(2,2);
620
insert into t2 values (1,1),(4,4);
622
UPDATE t2,t1 SET t2.a=t1.a+2;
623
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
624
select * from t2 /* must be (3,1), (4,4) */;
628
show master status /* there must be the UPDATE query event */;
629
File Position Binlog_Do_DB Binlog_Ignore_DB
630
master-bin.000001 198
633
insert into t1 values (1,2),(3,4),(4,4);
634
insert into t2 values (1,2),(3,4),(4,4);
636
UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a;
637
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'
638
show master status /* there must be the UPDATE query event */;
639
File Position Binlog_Do_DB Binlog_Ignore_DB
640
master-bin.000001 213
642
set @@session.binlog_format= @sav_binlog_format;
643
drop table if exists t1, t2, t3;
644
CREATE TABLE t1 (a int, PRIMARY KEY (a));
645
CREATE TABLE t2 (a int, PRIMARY KEY (a));
646
CREATE TABLE t3 (a int, PRIMARY KEY (a)) ENGINE=MyISAM;
647
create trigger trg_del_t3 before delete on t3 for each row insert into t1 values (1);
648
insert into t2 values (1),(2);
649
insert into t3 values (1),(2);
651
delete t3.* from t2,t3 where t2.a=t3.a;
652
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
653
select count(*) from t1 /* must be 1 */;
656
select count(*) from t3 /* must be 1 */;
659
drop table t1, t2, t3;
added
removed
mysql-test/r/multi_update.result
