multi_update.test 7.23 KB
Newer Older
1
#
2
# Test of update statement that uses many tables.
3 4
#

5
--disable_warnings
6
drop table if exists t1,t2,t3;
7 8
--enable_warnings

9
create table t1(id1 int not null auto_increment primary key, t char(12));
10
create table t2(id2 int not null, t char(12));
11
create table t3(id3 int not null, t char(12), index(id3));
12
disable_query_log;
13
let $1 = 100;
14 15
while ($1)
 {
16
  let $2 = 5;
17 18 19 20
  eval insert into t1(t) values ('$1'); 
  while ($2)
   {
     eval insert into t2(id2,t) values ($1,'$2'); 
21 22 23 24 25 26
     let $3 = 10;
     while ($3)
     {
       eval insert into t3(id3,t) values ($1,'$2'); 
       dec $3;
     }
27 28 29 30
     dec $2; 
   }
  dec $1;
 }
31
enable_query_log;
32 33 34 35 36

select count(*) from t1 where id1 > 95;
select count(*) from t2 where id2 > 95;
select count(*) from t3 where id3 > 95;

37
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;
38 39 40 41 42 43
select count(*) from t1 where t = "aaa";
select count(*) from t1 where id1 > 90;
select count(*) from t2 where t = "bbb";
select count(*) from t2 where id2 > 90;
select count(*) from t3 where t = "cc";
select count(*) from t3 where id3 > 90;
44
delete t1.*, t2.*, t3.*  from t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 95;
45 46 47

check table t1, t2, t3;

48 49 50
select count(*) from t1 where id1 > 95;
select count(*) from t2 where id2 > 95;
select count(*) from t3 where id3 > 95;
51

52 53 54 55
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;
select count(*) from t2 where id2 > 5;
select count(*) from t3 where id3 > 5;
56

57
delete from t1, t2, t3  using t1,t2,t3 where t1.id1 = t2.id2 and t2.id2 = t3.id3  and t1.id1 > 0;
58 59 60 61 62 63 64

# These queries will force a scan of the table
select count(*) from t1 where id1;
select count(*) from t2 where id2;
select count(*) from t3 where id3;

drop table t1,t2,t3;
65 66
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;
67 68 69 70 71
disable_query_log;
let $1 = 1000;
while ($1)
 {
  let $2 = 5;
72
  eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); 
73 74 75 76 77 78 79 80
  while ($2)
   {
     eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); 
     dec $2; 
   }
  dec $1;
 }
enable_query_log;
81
delete t1  from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500;
82
drop table t1,t2;
83 84

CREATE TABLE t1 (
85 86 87 88
  id int(11) NOT NULL default '0',
  name varchar(10) default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;
89 90
INSERT INTO t1 VALUES (1,'aaa'),(2,'aaa'),(3,'aaa');
CREATE TABLE t2 (
91 92 93 94
  id int(11) NOT NULL default '0',
  name varchar(10) default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;
95 96
INSERT INTO t2 VALUES (2,'bbb'),(3,'bbb'),(4,'bbb');
CREATE TABLE t3 (
97 98 99 100
  id int(11) NOT NULL default '0',
  mydate datetime default NULL,
  PRIMARY KEY  (id)
) TYPE=MyISAM;
101
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
102
00:00:00'),(7,'2002-07-22 00:00:00');
103 104
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;
105
DROP TABLE t1,t2,t3;
106 107

CREATE TABLE IF NOT EXISTS `t1` (
108 109 110 111 112 113
  `id` int(11) NOT NULL auto_increment,
  `tst` text,
  `tst1` text,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

114
CREATE TABLE IF NOT EXISTS `t2` (
115 116 117 118 119
  `ID` int(11) NOT NULL auto_increment,
  `ParId` int(11) default NULL,
  `tst` text,
  `tst1` text,
  PRIMARY KEY  (`ID`),
120 121
  KEY `IX_ParId_t2` (`ParId`),
  FOREIGN KEY (`ParId`) REFERENCES `t1` (`id`)
122 123
) TYPE=MyISAM;

124
INSERT INTO t1(tst,tst1) VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE");
125

126
INSERT INTO t2(ParId) VALUES(1), (2), (3);
127

128
select * from t2;
129

130
UPDATE t2, t1 SET t2.tst = t1.tst, t2.tst1 = t1.tst1 WHERE t2.ParId = t1.Id;
131

132
select * from t2;
133
drop table t1, t2 ;
134

Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
135 136 137 138 139 140
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);
delete  t1,t2 from t2 left outer join t1  using (n);
select * from t2 left outer join t1  using (n);
141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167
drop table t1,t2 ;

#
# Test with locking
#

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;
--error 1099
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
--error 1099
UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;
# The following should be fixed to not give an error
--error 1099
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;
DELETE t1.*, t2.* FROM t1,t2 where t1.n=t2.n;
select * from t1;
select * from t2;
unlock tables;
drop table t1,t2;
168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187

#
# Test safe updates and timestamps
#
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);
--error 1175
UPDATE t1,t2 SET t1.d=t2.d WHERE t1.n=t2.n;
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;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
188 189
select n,d,unix_timestamp(t) from t1;
select n,d,unix_timestamp(t) from t2;
190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217
--error 1064
UPDATE t1,t2 SET 1=2 WHERE t1.n=t2.n;
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;
select * from t2;
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;
select * from t2;
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;
select * from t2;
218
UPDATE t1 a ,t2 b SET a.d=b.d,b.d=30 WHERE a.n=b.n;
Sinisa@sinisa.nasamreza.org's avatar
Sinisa@sinisa.nasamreza.org committed
219 220 221 222 223
select * from t1;
select * from t2;
DELETE t1, t2  FROM t1 a,t2 b where a.n=b.n;
select * from t1;
select * from t2;
224
drop table t1,t2;
225

226 227 228 229 230 231 232 233
CREATE TABLE t1 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) TYPE=MyISAM;
INSERT INTO t1 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a'),(10,''),(11,''),(12,''),(13,'');
CREATE TABLE t2 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) TYPE=MyISAM;
INSERT INTO t2 VALUES (1,'jedan'),(2,'dva'),(3,'tri'),(4,'xxxxxxxxxx'),(5,'a');
CREATE TABLE t3 ( broj int(4) unsigned NOT NULL default '0',  naziv char(25) NOT NULL default 'NEPOZNAT',  PRIMARY KEY  (broj)) TYPE=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;
234
drop table t1,t2,t3;