delete.result 4.24 KB
Newer Older
1
drop table if exists t1,t11,t12,t2;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
CREATE TABLE t1 (a tinyint(3), b tinyint(5));
INSERT INTO t1 VALUES (1,1);
INSERT LOW_PRIORITY INTO t1 VALUES (1,2);
INSERT INTO t1 VALUES (1,3);
DELETE from t1 where a=1 limit 1;
DELETE LOW_PRIORITY from t1 where a=1;
INSERT INTO t1 VALUES (1,1);
DELETE from t1;
LOCK TABLE t1 write;
INSERT INTO t1 VALUES (1,2);
DELETE from t1;
UNLOCK TABLES;
INSERT INTO t1 VALUES (1,2);
SET AUTOCOMMIT=0;
DELETE from t1;
SET AUTOCOMMIT=1;
drop table t1;
19 20 21 22 23 24 25 26 27 28 29 30 31
create table t1 (
a bigint not null,
b bigint not null default 0,
c bigint not null default 0,
d bigint not null default 0,
e bigint not null default 0,
f bigint not null default 0,
g bigint not null default 0,
h bigint not null default 0,
i bigint not null default 0,
j bigint not null default 0,
primary key (a,b,c,d,e,f,g,h,i,j));
insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23);
32 33
delete from t1 where a=26;
drop table t1;
34 35 36 37 38 39 40 41 42 43 44 45 46
create table t1 (
a bigint not null,
b bigint not null default 0,
c bigint not null default 0,
d bigint not null default 0,
e bigint not null default 0,
f bigint not null default 0,
g bigint not null default 0,
h bigint not null default 0,
i bigint not null default 0,
j bigint not null default 0,
primary key (a,b,c,d,e,f,g,h,i,j));
insert into t1 (a) values (2),(4),(6),(8),(10),(12),(14),(16),(18),(20),(22),(24),(26),(23),(27);
47 48
delete from t1 where a=27;
drop table t1;
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
49
CREATE TABLE `t1` (
50 51 52
`i` int(10) NOT NULL default '0',
`i2` int(10) NOT NULL default '0',
PRIMARY KEY  (`i`)
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
53 54
);
DELETE FROM t1 USING t1 WHERE post='1';
55
ERROR 42S22: Unknown column 'post' in 'where clause'
monty@narttu.mysql.fi's avatar
monty@narttu.mysql.fi committed
56
drop table t1;
57 58 59 60 61
CREATE TABLE t1 (
bool     char(0) default NULL,
not_null varchar(20) binary NOT NULL default '',
misc     integer not null,
PRIMARY KEY  (not_null)
62
) ENGINE=MyISAM;
63 64 65 66 67 68 69 70
INSERT INTO t1 VALUES (NULL,'a',4), (NULL,'b',5), (NULL,'c',6), (NULL,'d',7);
select * from t1 where misc > 5 and bool is null;
bool	not_null	misc
NULL	c	6
NULL	d	7
delete   from t1 where misc > 5 and bool is null;
select * from t1 where misc > 5 and bool is null;
bool	not_null	misc
serg@serg.mylan's avatar
serg@serg.mylan committed
71 72 73 74 75 76 77 78 79 80 81
select count(*) from t1;
count(*)
2
delete from t1 where 1 > 2;
select count(*) from t1;
count(*)
2
delete from t1 where 3 > 2;
select count(*) from t1;
count(*)
0
82
drop table t1;
monty@mysql.com's avatar
monty@mysql.com committed
83 84 85 86 87 88 89 90 91 92 93
create table t1 (a int not null auto_increment primary key, b char(32));
insert into t1 (b) values ('apple'), ('apple');
select * from t1;
a	b
1	apple
2	apple
delete t1 from t1, t1 as t2 where t1.b = t2.b and t1.a > t2.a;
select * from t1;
a	b
1	apple
drop table t1;
94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
create table t11 (a int NOT NULL, b int, primary key (a));
create table t12 (a int NOT NULL, b int, primary key (a));
create table t2 (a int NOT NULL, b int, primary key (a));
insert into t11 values (0, 10),(1, 11),(2, 12);
insert into t12 values (33, 10),(0, 11),(2, 12);
insert into t2 values (1, 21),(2, 12),(3, 23);
select * from t11;
a	b
0	10
1	11
2	12
select * from t12;
a	b
33	10
0	11
2	12
select * from t2;
a	b
1	21
2	12
3	23
delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 21000: Subquery returns more than 1 row
select * from t11;
a	b
0	10
1	11
2	12
select * from t12;
a	b
33	10
0	11
2	12
delete ignore t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b <> (select b from t2 where t11.a < t2.a);
Warnings:
monty@mysql.com's avatar
monty@mysql.com committed
129 130
Error	1242	Subquery returns more than 1 row
Error	1242	Subquery returns more than 1 row
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148
select * from t11;
a	b
0	10
1	11
select * from t12;
a	b
33	10
0	11
insert into t11 values (2, 12);
delete from t11 where t11.b <> (select b from t2 where t11.a < t2.a);
ERROR 21000: Subquery returns more than 1 row
select * from t11;
a	b
0	10
1	11
2	12
delete ignore from t11 where t11.b <> (select b from t2 where t11.a < t2.a);
Warnings:
monty@mysql.com's avatar
monty@mysql.com committed
149 150
Error	1242	Subquery returns more than 1 row
Error	1242	Subquery returns more than 1 row
151 152 153 154 155
select * from t11;
a	b
0	10
1	11
drop table t11, t12, t2;
156 157 158 159 160 161 162 163 164 165
create table t1 (a int, b int, unique key (a), key (b));
insert into t1 values (3, 3), (7, 7);
delete t1 from t1 where a = 3;
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
select * from t1;
a	b
7	7
drop table t1;
166 167 168 169 170 171 172 173 174
CREATE TABLE t1 ( a int PRIMARY KEY );
DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a;
INSERT INTO t1 VALUES (0),(1),(2);
DELETE FROM t1 WHERE t1.a > 0 ORDER BY t1.a LIMIT 1;
SELECT * FROM t1;
a
0
2
DROP TABLE t1;