~drizzle-trunk/drizzle/development

delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);

947

select * from t1;

948

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

949

6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0

950

333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0

951

select * from t2;

952

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

953

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

954

select * from t2;

955

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

956

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

957

drop table t1,t2;

958

896

create table t1 (id int not null auto_increment, code int not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;

959

897

BEGIN;

960

898

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

993

931

7 4 Matt

994

932

COMMIT;

995

933

DROP TABLE t1;

996

create table t1 (n int, d int) engine=innodb;

997

create table t2 (n int, d int) engine=innodb;

998

insert into t1 values(1,1),(1,2);

999

insert into t2 values(1,10),(2,20);

1000

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

1001

select * from t1;

1002

n d

1003

1 10

1004

1 10

1005

select * from t2;

1006

n d

1007

1 30

1008

2 20

1009

drop table t1,t2;

1010

934

create table t1 (a int, b int) engine=innodb;

1011

935

insert into t1 values(20,null);

1012

936

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

1033

957

create table t2 (a int not null, b int, primary key (a)) engine = innodb;

1034

958

insert into t1 values (10, 20);

1035

959

insert into t2 values (10, 20);

1036

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

1037

960

drop table t1,t2;

1038

CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

1039

CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (t1_id) REFERENCES t1(id) ON DELETE CASCADE ) ENGINE=INNODB;

1040

insert into t1 set id=1;

1041

insert into t2 set id=1, t1_id=1;

1042

delete t1,t2 from t1,t2 where t1.id=t2.t1_id;

1043

select * from t1;

1044

1045

select * from t2;

1046

id t1_id

1047

drop table t2,t1;

1048

CREATE TABLE t1(id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;

1049

CREATE TABLE t2(id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id) ) ENGINE=INNODB;

1050

INSERT INTO t1 VALUES(1);

1051

INSERT INTO t2 VALUES(1, 1);

1052

SELECT * from t1;

1053

1054

1055

UPDATE t1,t2 SET t1.id=t1.id+1, t2.t1_id=t1.id+1;

1056

SELECT * from t1;

1057

1058

1059

UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;

1060

SELECT * from t1;

1061

1062

1063

DROP TABLE t1,t2;

1064

961

set autocommit=0;

1065

962

CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

1066

963

CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;

1107

1004

102 2

1108

1005

103 3

1109

1006

drop table t1;

1110

CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;

1111

CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;

1112

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);

1113

INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

1114

update t1,t2 set t1.a=t1.a+100;

1115

select * from t1;

1116

a b

1117

101 1

1118

102 2

1119

103 3

1120

104 4

1121

105 5

1122

106 6

1123

107 7

1124

108 8

1125

109 9

1126

110 10

1127

111 11

1128

112 12

1129

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

1130

select * from t1;

1131

a b

1132

102 2

1133

103 3

1134

104 4

1135

105 5

1136

106 6

1137

107 7

1138

108 8

1139

109 9

1140

110 10

1141

111 11

1142

112 12

1143

201 1

1144

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

1145

select * from t1;

1146

a b

1147

102 12

1148

103 3

1149

104 4

1150

105 5

1151

106 6

1152

107 7

1153

108 8

1154

109 9

1155

110 10

1156

111 11

1157

112 12

1158

201 1

1159

update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;

1160

select * from t1;

1161

a b

1162

102 12

1163

103 5

1164

104 6

1165

105 7

1166

106 6

1167

107 7

1168

108 8

1169

109 9

1170

110 10

1171

111 11

1172

112 12

1173

201 1

1174

select * from t2;

1175

a b

1176

1 1

1177

2 2

1178

3 13

1179

4 14

1180

5 15

1181

6 6

1182

7 7

1183

8 8

1184

9 9

1185

drop table t1,t2;

1186

CREATE TEMPORARY TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

1187

CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;

1188

SET AUTOCOMMIT=0;

1189

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

1190

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

1191

ROLLBACK;

1192

Warnings:

1193

Warning 1196 Some non-transactional changed tables couldn't be rolled back

1194

SELECT * FROM t1;

1195

B_ID

1196

drop table t1,t2;

1197

create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = innodb;

1198

insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);

1199

select distinct parent,child from t1 order by parent;

1200

parent child

1201

0 4

1202

1 2

1203

1 3

1204

2 1

1205

drop table t1;

1206

1007

create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=innodb;

1207

1008

create table t2 (a int not null auto_increment primary key, b int);

1208

1009

insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);

1260

1061

111 100

1261

1062

111 100

1262

1063

drop table t1;

1263

create table t1 ( c char(8) not null ) engine=innodb;

1264

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

1265

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

1266

alter table t1 add b char(8) not null;

1267

alter table t1 add a char(8) not null;

1268

alter table t1 add primary key (a,b,c);

1269

update t1 set a=c, b=c;

1270

create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=innodb;

1271

insert into t2 select * from t1;

1272

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

1273

drop table t1,t2;

1274

1064

SET AUTOCOMMIT=1;

1275

1065

create table t1 (a integer auto_increment primary key) engine=innodb;

1276

1066

insert into t1 (a) values (NULL),(NULL);

1284

1074

CREATE TABLE t1 (`id 1` INT NOT NULL, PRIMARY KEY (`id 1`)) ENGINE=INNODB;

1285

1075

CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind (t1_id), FOREIGN KEY (`t1_id`) REFERENCES `t1`(`id 1`) ON DELETE CASCADE ) ENGINE=INNODB;

1286

1076

drop table t2,t1;

1287

create table `t1` (`id` int not null ,primary key ( `id` )) engine = innodb;

1288

insert into `t1`values ( 1 ) ;

1289

create table `t2` (`id` int not null default '0',unique key `id` ( `id` ) ,constraint `t1_id_fk` foreign key ( `id` ) references `t1` (`id` )) engine = innodb;

1290

insert into `t2`values ( 1 ) ;

1291

create table `t3` (`id` int not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb;

1292

insert into `t3`values ( 1 ) ;

1293

delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

1294

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))

1295

update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

1296

ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`))

1297

update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id;

1298

ERROR 42S22: Unknown column 't1.id' in 'where clause'

1299

drop table t3,t2,t1;

1300

1077

create table t1(

1301

1078

id int primary key,

1302

1079

pid int,

2373

2150

GRADE

2374

2151

151

2375

2152

DROP TABLE t1;

2376

create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;

2377

create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;

2378

insert into t2 values ('aa','cc');

2379

insert into t1 values ('aa','bb'),('aa','cc');

2380

delete t1 from t1,t2 where f1=f3 and f4='cc';

2381

select * from t1;

2382

f1 f2

2383

drop table t1,t2;

2384

2153

CREATE TABLE t1 (

2385

2154

id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)

2386

2155

) ENGINE=InnoDB;

2773

2542

INSERT INTO t2 VALUES (1, 0, '');

2774

2543

INSERT INTO t2 VALUES (8, 1, '');

2775

2544

commit;

2776

DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)

2777

WHERE mm.id IS NULL;

2778

2545

select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)

2779

2546

where mm.id is null lock in share mode;

2780

2547

id f_id f

Older »