multi_update.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) ENGINE=MyISAM;
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;
drop table t1,t2,t3;
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
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;
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
1	1
2	2
3	13
4	14
5	15
6	6
7	7
8	8
9	9
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);
drop table t1,t2;
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;
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;
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;
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;
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;
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
drop table t1,t2;
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;
ERROR 42S02: Unknown table 't1' in MULTI DELETE
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
1
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
1
SELECT * from t2;
a
2
DROP TABLE t1,t2;
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);
update t1 left join t2 on p_id = c2_p_id set c2_note = 'asdf-1' where p_id = 2;
select * from t1;
p_id	p_code	p_active
1	A01-Comp	1
2	B01-Comp	1
select * from t2;
c2_id	c2_p_id	c2_note	c2_active
1	1	A Note	1
drop table t1, t2;
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;
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user=_binary'mysqltest_1';
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;
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;
ERROR HY000: You can't specify target table 't1' for update in FROM clause
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;
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;
insert into t2 values(1,null);
delete t2, t1 from t2 left join t1 on (t2.aclid=t1.aclid) where t2.refid='1';
drop table t1, t2;
create table t1 ( c char(8) not null ) engine=innodb;
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;
create table t1 ( c char(8) not null ) engine=bdb;
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;
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;
 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;
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;