multi_update.result 18.6 KB
Newer Older
unknown's avatar
unknown committed
1
drop table if exists t1,t2,t3;
2
drop database if exists mysqltest;
unknown's avatar
unknown committed
3
drop view if exists v1;
unknown's avatar
unknown committed
4 5 6
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user=_binary'mysqltest_1';
unknown's avatar
unknown committed
7 8 9
create table t1(id1 int not null auto_increment primary key, t char(12));
create table t2(id2 int not null, t char(12));
create table t3(id3 int not null, t char(12), index(id3));
10 11 12 13 14 15 16 17 18
select count(*) from t1 where id1 > 95;
count(*)
5
select count(*) from t2 where id2 > 95;
count(*)
25
select count(*) from t3 where id3 > 95;
count(*)
250
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 21 22 23 24 25 26 27
select count(*) from t1 where t = "aaa";
count(*)
10
select count(*) from t1 where id1 > 90;
count(*)
10
select count(*) from t2 where t = "bbb";
count(*)
unknown's avatar
unknown committed
28
50
29 30 31 32 33 34 35 36 37
select count(*) from t2 where id2 > 90;
count(*)
50
select count(*) from t3 where t = "cc";
count(*)
500
select count(*) from t3 where id3 > 90;
count(*)
500
38
delete t1.*, t2.*, t3.*  from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 95;
unknown's avatar
unknown committed
39
check table t1, t2, t3;
40 41 42 43
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
test.t2	check	status	OK
test.t3	check	status	OK
44
select count(*) from t1 where id1 > 95;
45 46
count(*)
0
47
select count(*) from t2 where id2 > 95;
48 49
count(*)
0
50
select count(*) from t3 where id3 > 95;
51 52
count(*)
0
53 54
delete t1, t2, t3  from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 5;
select count(*) from t1 where id1 > 5;
55 56
count(*)
0
57
select count(*) from t2 where id2 > 5;
58 59
count(*)
0
60
select count(*) from t3 where id3 > 5;
61 62
count(*)
0
63
delete from t1, t2, t3  using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 0;
unknown's avatar
unknown committed
64
select count(*) from t1 where id1;
65 66
count(*)
0
unknown's avatar
unknown committed
67
select count(*) from t2 where id2;
68 69
count(*)
0
unknown's avatar
unknown committed
70
select count(*) from t3 where id3;
71 72
count(*)
0
unknown's avatar
unknown committed
73
drop table t1,t2,t3;
74 75 76
create table t1(id1 int not null  primary key, t varchar(100)) pack_keys = 1;
create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1;
delete t1  from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
77
drop table t1,t2;
unknown's avatar
unknown committed
78
CREATE TABLE t1 (
79 80 81
id int(11) NOT NULL default '0',
name varchar(10) default NULL,
PRIMARY KEY  (id)
unknown's avatar
unknown committed
82
) ENGINE=MyISAM;
unknown's avatar
unknown committed
83 84
INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
CREATE TABLE t2 (
85 86 87
id int(11) NOT NULL default '0',
name varchar(10) default NULL,
PRIMARY KEY  (id)
unknown's avatar
unknown committed
88
) ENGINE=MyISAM;
unknown's avatar
unknown committed
89 90
INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
CREATE TABLE t3 (
91 92 93
id int(11) NOT NULL default '0',
mydate datetime default NULL,
PRIMARY KEY  (id)
unknown's avatar
unknown committed
94
) ENGINE=MyISAM;
unknown's avatar
unknown committed
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');
unknown's avatar
unknown committed
97 98
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;
select * from t3;
99 100 101 102 103
id	mydate
1	2002-02-04 00:00:00
5	2002-05-12 00:00:00
6	2002-06-22 00:00:00
7	2002-07-22 00:00:00
104
DROP TABLE t1,t2,t3;
unknown's avatar
unknown committed
105
CREATE TABLE IF NOT EXISTS `t1` (
106 107 108 109
`id` int(11) NOT NULL auto_increment,
`tst` text,
`tst1` text,
PRIMARY KEY  (`id`)
unknown's avatar
unknown committed
110
) ENGINE=MyISAM;
unknown's avatar
unknown committed
111
CREATE TABLE IF NOT EXISTS `t2` (
112 113 114 115 116
`ID` int(11) NOT NULL auto_increment,
`ParId` int(11) default NULL,
`tst` text,
`tst1` text,
PRIMARY KEY  (`ID`),
unknown's avatar
unknown committed
117 118
KEY `IX_ParId_t2` (`ParId`),
FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)
unknown's avatar
unknown committed
119
) ENGINE=MyISAM;
unknown's avatar
unknown committed
120 121 122
INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
INSERT INTO t2(ParId) VALUES(1), (2), (3);
select * from t2;
123 124 125 126
ID	ParId	tst	tst1
1	1	NULL	NULL
2	2	NULL	NULL
3	3	NULL	NULL
unknown's avatar
unknown committed
127 128
UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;
select * from t2;
129 130 131 132
ID	ParId	tst	tst1
1	1	MySQL	MySQL AB
2	2	MSSQL	Microsoft
3	3	ORACLE	ORACLE
133
drop table t1, t2 ;
unknown's avatar
unknown committed
134 135 136 137
create table t1 (n numeric(10));
create table t2 (n numeric(10));
insert into t2 values (1),(2),(4),(8),(16),(32);
select * from t2 left outer join t1  using (n);
unknown's avatar
unknown committed
138 139 140 141 142 143 144
n
1
2
4
8
16
32
unknown's avatar
unknown committed
145 146
delete  t1,t2 from t2 left outer join t1  using (n);
select * from t2 left outer join t1  using (n);
unknown's avatar
unknown committed
147
n
unknown's avatar
unknown committed
148 149 150 151 152 153 154
drop table t1,t2 ;
create table t1 (n int(10) not null primary key, d int(10));
create table t2 (n int(10) not null primary key, d int(10));
insert into t1 values(1,1);
insert into t2 values(1,10),(2,20);
LOCK TABLES t1 write, t2 read;
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
unknown's avatar
unknown committed
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
unknown's avatar
unknown committed
158 159 160 161 162 163 164 165 166 167 168 169 170 171 172
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
unlock tables;
LOCK TABLES t1 write, t2 write;
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
select * from t1;
n	d
1	10
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
select * from t1;
n	d
select * from t2;
n	d
2	20
unlock tables;
drop table t1,t2;
173 174 175 176 177 178
set sql_safe_updates=1;
create table t1 (n int(10), d int(10));
create table t2 (n int(10), d int(10));
insert into t1 values(1,1);
insert into t2 values(1,10),(2,20);
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 181 182 183 184 185 186 187 188
set sql_safe_updates=0;
drop table t1,t2;
set timestamp=1038401397;
create table t1 (n int(10) not null primary key, d int(10), t timestamp);
create table t2 (n int(10) not null primary key, d int(10), t timestamp);
insert into t1 values(1,1,NULL);
insert into t2 values(1,10,NULL),(2,20,NULL);
set timestamp=1038000000;
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
unknown's avatar
unknown committed
189 190 191 192 193 194 195
select n,d,unix_timestamp(t) from t1;
n	d	unix_timestamp(t)
1	10	1038000000
select n,d,unix_timestamp(t) from t2;
n	d	unix_timestamp(t)
1	10	1038401397
2	20	1038401397
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
198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241
drop table t1,t2;
set timestamp=0;
set sql_safe_updates=0;
create table t1 (n int(10) not null primary key, d int(10));
create table t2 (n int(10) not null primary key, d int(10));
insert into t1 values(1,1), (3,3);
insert into t2 values(1,10),(2,20);
UPDATE t2 left outer join t1 on t1.n=t2.n  SET t1.d=t2.d;
select * from t1;
n	d
1	10
3	3
select * from t2;
n	d
1	10
2	20
drop table t1,t2;
create table t1 (n int(10), d int(10));
create table t2 (n int(10), d int(10));
insert into t1 values(1,1),(1,2);
insert into t2 values(1,10),(2,20);
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
select * from t1;
n	d
1	10
1	10
select * from t2;
n	d
1	30
2	20
drop table t1,t2;
create table t1 (n int(10), d int(10));
create table t2 (n int(10), d int(10));
insert into t1 values(1,1),(3,2);
insert into t2 values(1,10),(1,20);
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
select * from t1;
n	d
1	10
3	2
select * from t2;
n	d
1	30
1	30
242
UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
unknown's avatar
unknown committed
243 244 245 246 247 248 249 250
select * from t1;
n	d
1	30
3	2
select * from t2;
n	d
1	30
1	30
251
DELETE a, b  FROM t1 a,t2 b where a.n=b.n;
unknown's avatar
unknown committed
252 253 254 255 256
select * from t1;
n	d
3	2
select * from t2;
n	d
257
drop table t1,t2;
unknown's avatar
unknown committed
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,'');
unknown's avatar
unknown committed
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');
unknown's avatar
unknown committed
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 264 265
INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');
update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;
update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;
266
drop table t1,t2,t3;
267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306
CREATE TABLE t1 (a int not null primary key, b int not null, key (b));
CREATE TABLE t2 (a int not null primary key, b int not null, key (b));
INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
update t1,t2 set t1.a=t1.a+100;
select * from t1;
a	b
101	1
102	2
103	3
104	4
105	5
106	6
107	7
108	8
109	9
update t1,t2 set t1.a=t1.a+100 where t1.a=101;
select * from t1;
a	b
201	1
102	2
103	3
104	4
105	5
106	6
107	7
108	8
109	9
update t1,t2 set t1.b=t1.b+10 where t1.b=2;
select * from t1;
a	b
201	1
102	12
103	3
104	4
105	5
106	6
107	7
108	8
109	9
unknown's avatar
unknown committed
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;
308 309 310 311 312 313 314 315 316 317 318 319 320
select * from t1;
a	b
201	1
102	12
103	5
104	6
105	7
106	6
107	7
108	8
109	9
select * from t2;
a	b
unknown's avatar
unknown committed
321 322 323 324 325 326 327 328 329
1	1
2	2
3	13
4	14
5	15
6	6
7	7
8	8
9	9
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);
331
drop table t1,t2;
unknown's avatar
unknown committed
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 334 335 336 337 338 339
INSERT INTO t3 VALUES ('A',1,1,22,3.2,'R','R','BASE2',0.24,NULL);
create table t1 (A varchar(1));
insert into t1 values  ("A") ,("B"),("C"),("D");
create table t2(Z varchar(15));
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;
update t2,t3 set Z =param_scenario_costs;
drop table t1,t2,t3;
unknown's avatar
unknown committed
340 341 342 343 344 345 346 347 348
create table t1 (a int, b int);
create table t2 (a int, b int);
insert into t1 values (1,1),(2,1),(3,1);
insert into t2 values (1,1), (3,1);
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;
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;
a	b	a	b
2	2	NULL	NULL
drop table t1,t2;
349 350 351 352 353 354 355 356 357 358
create table t1 (a int not null auto_increment primary key, b int not null);
insert into t1 (b) values (1),(2),(3),(4);
update t1, t1 as t2 set t1.b=t2.b+1 where t1.a=t2.a;
select * from t1;
a	b
1	2
2	3
3	4
4	5
drop table t1;
unknown's avatar
unknown committed
359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380
create table t1(id1 smallint(5), field char(5));
create table t2(id2 smallint(5), field char(5));
insert into t1 values (1, 'a'), (2, 'aa');
insert into t2 values (1, 'b'), (2, 'bb');
select * from t1;
id1	field
1	a
2	aa
select * from t2;
id2	field
1	b
2	bb
update t2 inner join t1 on t1.id1=t2.id2
set t2.field=t1.field
where 0=1;
update t2, t1 set t2.field=t1.field
where t1.id1=t2.id2 and 0=1;
delete t1, t2 from t2 inner join t1 on t1.id1=t2.id2
where 0=1;
delete t1, t2 from t2,t1 
where t1.id1=t2.id2 and 0=1;
drop table t1,t2;
unknown's avatar
unknown committed
381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402
create table t1 ( a int not null, b int not null) ;
alter table t1 add index i1(a);
delete from t1 where a > 2000000;
create table t2 like t1;
insert into t2 select * from t1;
select 't2 rows before small delete', count(*) from t1;
t2 rows before small delete	count(*)
t2 rows before small delete	2000000
delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 2;
select 't2 rows after small delete', count(*) from t2;
t2 rows after small delete	count(*)
t2 rows after small delete	1999999
select 't1 rows after small delete', count(*) from t1;
t1 rows after small delete	count(*)
t1 rows after small delete	1999999
delete t1,t2 from t1,t2 where t1.b=t2.a and t1.a < 100*1000;
select 't2 rows after big delete', count(*) from t2;
t2 rows after big delete	count(*)
t2 rows after big delete	1900001
select 't1 rows after big delete', count(*) from t1;
t1 rows after big delete	count(*)
t1 rows after big delete	1900001
unknown's avatar
unknown committed
403
drop table t1,t2;
404 405 406 407 408 409
CREATE TABLE t1 ( a int );
CREATE TABLE t2 ( a int );
DELETE t1 FROM t1, t2 AS t3;
DELETE t4 FROM t1, t1 AS t4;
DELETE t3 FROM t1 AS t3, t1 AS t4;
DELETE t1 FROM t1 AS t3, t2 AS t4;
410
ERROR 42S02: Unknown table 't1' in MULTI DELETE
411 412 413 414 415
INSERT INTO t1 values (1),(2);
INSERT INTO t2 values (1),(2);
DELETE t1 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=1;
SELECT * from t1;
a
unknown's avatar
unknown committed
416
1
417 418 419 420 421 422 423
2
SELECT * from t2;
a
2
DELETE t2 FROM t1 AS t2, t2 AS t1 where t1.a=t2.a and t1.a=2;
SELECT * from t1;
a
unknown's avatar
unknown committed
424
1
425 426
SELECT * from t2;
a
unknown's avatar
unknown committed
427
2
428
DROP TABLE t1,t2;
unknown's avatar
unknown committed
429 430 431 432 433
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`) );
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`) );
insert into t1 values (0,'A01-Comp',1);
insert into t1 values (0,'B01-Comp',1);
insert into t2 values (0,1,'A Note',1);
unknown's avatar
unknown committed
434 435
update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2;
select * from t1;
unknown's avatar
unknown committed
436 437 438
p_id	p_code	p_active
1	A01-Comp	1
2	B01-Comp	1
unknown's avatar
unknown committed
439
select * from t2;
unknown's avatar
unknown committed
440 441
c2_id	c2_p_id	c2_note	c2_active
1	1	A Note	1
unknown's avatar
unknown committed
442
drop table t1, t2;
443 444 445 446 447 448 449 450 451
create database mysqltest;
create table mysqltest.t1 (a int, b int, primary key (a));
create table mysqltest.t2 (a int, b int, primary key (a));
create table mysqltest.t3 (a int, b int, primary key (a));
grant select on mysqltest.* to mysqltest_1@localhost;
grant update on mysqltest.t1 to mysqltest_1@localhost;
update t1, t2 set t1.b=1 where t1.a=t2.a;
update t1, t2 set t1.b=(select t3.b from t3 where t1.a=t3.a) where t1.a=t2.a;
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 455 456 457 458 459 460
drop database mysqltest;
create table t1 (a int, primary key (a));
create table t2 (a int, primary key (a));
create table t3 (a int, primary key (a));
delete t1,t3 from t1,t2 where t1.a=t2.a and t2.a=(select t3.a from t3 where t1.a=t3.a);
ERROR 42S02: Unknown table 't3' in MULTI DELETE
drop table t1, t2, t3;
461 462 463
create table t1 (col1 int);
create table t2 (col1 int);
update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
unknown's avatar
unknown committed
464
ERROR HY000: You can't specify target table 't1' for update in FROM clause
465 466 467
delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
ERROR HY000: You can't specify target table 't1' for update in FROM clause
drop table t1,t2;
unknown's avatar
unknown committed
468 469 470 471 472 473 474 475
create table t1 (
aclid bigint not null primary key, 
status tinyint(1) not null 
) engine = innodb;
create table t2 (
refid bigint not null primary key, 
aclid bigint, index idx_acl(aclid) 
) engine = innodb;
unknown's avatar
unknown committed
476
insert into t2 values(1,null);
unknown's avatar
unknown committed
477
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
unknown's avatar
unknown committed
478
drop table t1, t2;
479 480 481 482
create table t1(a int);
create table t2(a int);
delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
ERROR HY000: You can't specify target table 't1' for update in FROM clause
483
drop table t1, t2;
484
create table t1 ( c char(8) not null ) engine=innodb;
unknown's avatar
unknown committed
485 486 487 488 489 490 491 492 493 494
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
alter table t1 add b char(8) not null;
alter table t1 add a char(8) not null;
alter table t1 add primary key (a,b,c);
update t1 set a=c, b=c;
create table t2 like t1;
insert into t2 select * from t1;
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
drop table t1,t2;
495
create table t1 ( c char(8) not null ) engine=innodb;
unknown's avatar
unknown committed
496 497 498 499 500 501 502 503 504 505
insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');
alter table t1 add b char(8) not null;
alter table t1 add a char(8) not null;
alter table t1 add primary key (a,b,c);
update t1 set a=c, b=c;
create table t2 like t1;
insert into t2 select * from t1;
delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;
drop table t1,t2;
unknown's avatar
unknown committed
506 507 508 509 510 511
create table t1 (a int, b int);
insert into t1 values (1, 2), (2, 3), (3, 4);
create table t2 (a int);
insert into t2 values (10), (20), (30);
create view v1 as select a as b, a/10 as a from t2;
lock table t1 write;
512 513
alter table t1 add column c int default 100 after a;
update t1, v1 set t1.b=t1.a+t1.b+v1.b where t1.a=v1.a;
unknown's avatar
unknown committed
514 515 516 517 518 519 520 521 522 523 524 525 526
unlock tables;
select * from t1;
a	c	b
1	100	13
2	100	25
3	100	37
select * from t2;
a
10
20
30
drop view v1;
drop table t1, t2;
527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606
create table t1 (i1 int, i2 int, i3 int);
create table t2 (id int, c1 varchar(20), c2 varchar(20));
insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
select * from t1 order by i1;
i1	i2	i3
1	5	10
2	2	2
3	7	12
4	5	2
9	10	15
select * from t2;
id	c1	c2
9	abc	def
5	opq	lmn
2	test t	t test
update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
select * from t1 order by i1;
i1	i2	i3
1	5	10
2	15	2
3	7	12
4	5	2
9	15	15
select * from t2 order by id;
id	c1	c2
2	test t	ppc
5	opq	lmn
9	abc	ppc
delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
select * from t1 order by i1;
i1	i2	i3
2	15	2
3	7	12
9	15	15
select * from t2 order by id;
id	c1	c2
2	test t	ppc
9	abc	ppc
drop table t1, t2;
create table t1 (i1 int auto_increment not null, i2 int, i3 int, primary key (i1));
create table t2 (id int auto_increment not null, c1 varchar(20), c2 varchar(20), primary key(id));
insert into t1 values (1,5,10),(3,7,12),(4,5,2),(9,10,15),(2,2,2);
insert into t2 values (9,"abc","def"),(5,"opq","lmn"),(2,"test t","t test");
select * from t1 order by i1;
i1	i2	i3
1	5	10
2	2	2
3	7	12
4	5	2
9	10	15
select * from t2 order by id;
id	c1	c2
2	test t	t test
5	opq	lmn
9	abc	def
update t1,t2 set t1.i2=15, t2.c2="ppc" where t1.i1=t2.id;
select * from t1 order by i1;
i1	i2	i3
1	5	10
2	15	2
3	7	12
4	5	2
9	15	15
select * from t2 order by id;
id	c1	c2
2	test t	ppc
5	opq	lmn
9	abc	ppc
delete t1.*,t2.* from t1,t2 where t1.i2=t2.id;
select * from t1 order by i1;
i1	i2	i3
2	15	2
3	7	12
9	15	15
select * from t2 order by id;
id	c1	c2
2	test t	ppc
9	abc	ppc
drop table t1, t2;
607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640
CREATE TABLE `t1` (
`a` int(11) NOT NULL auto_increment,
`b` int(11) default NULL,
PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
CREATE TABLE `t2` (
`a` int(11) NOT NULL auto_increment,
`b` int(11) default NULL,
PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
insert into t1 values (1,1),(2,2);
insert into t2 values (1,1),(4,4);
reset master;
UPDATE t2,t1 SET t2.a=t1.a+2;
ERROR 23000: Duplicate entry '3' for key 1
select * from t2 /* must be (3,1), (4,4) */;
a	b
3	1
4	4
show master status /* there must be the UPDATE query event */;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
master-bin.000001	189		
delete from t1;
delete from t2;
insert into t1 values (1,2),(3,4),(4,4);
insert into t2 values (1,2),(3,4),(4,4);
reset master;
UPDATE t2,t1  SET t2.a=t2.b where t2.a=t1.a;
ERROR 23000: Duplicate entry '4' for key 1
show master status /* there must be the UPDATE query event */;
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
master-bin.000001	204		
drop table t1, t2;
end of tests