insert_update.result 3.88 KB
Newer Older
1 2 3
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
INSERT t1 VALUES (1,2,10), (3,4,20);
4
INSERT t1 VALUES (5,6,30) ON DUPLICATE KEY UPDATE c=c+100;
5 6 7 8 9
SELECT * FROM t1;
a	b	c
1	2	10
3	4	20
5	6	30
10
INSERT t1 VALUES (5,7,40) ON DUPLICATE KEY UPDATE c=c+100;
11 12 13 14 15
SELECT * FROM t1;
a	b	c
1	2	10
3	4	20
5	6	130
16
INSERT t1 VALUES (8,4,50) ON DUPLICATE KEY UPDATE c=c+1000;
17 18 19 20 21
SELECT * FROM t1;
a	b	c
1	2	10
3	4	1020
5	6	130
22
INSERT t1 VALUES (1,4,60) ON DUPLICATE KEY UPDATE c=c+10000;
23 24 25 26 27
SELECT * FROM t1;
a	b	c
1	2	10010
3	4	1020
5	6	130
28
INSERT t1 VALUES (1,9,70) ON DUPLICATE KEY UPDATE c=c+100000, b=4;
29
ERROR 23000: Duplicate entry '4' for key 2
30 31 32 33 34 35 36
SELECT * FROM t1;
a	b	c
1	2	10010
3	4	1020
5	6	130
TRUNCATE TABLE t1;
INSERT t1 VALUES (1,2,10), (3,4,20);
37
INSERT t1 VALUES (5,6,30), (7,4,40), (8,9,60) ON DUPLICATE KEY UPDATE c=c+100;
38 39 40 41 42 43
SELECT * FROM t1;
a	b	c
1	2	10
3	4	120
5	6	30
8	9	60
44
INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
serg@serg.mysql.com's avatar
serg@serg.mysql.com committed
45 46 47 48 49 50
SELECT * FROM t1;
a	b	c
1	2	10
3	4	120
5	0	30
8	9	60
51 52 53 54 55 56 57 58
INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a);
SELECT *, VALUES(a) FROM t1;
a	b	c	VALUES(a)
1	2	10	NULL
3	4	127	NULL
5	0	30	NULL
8	9	60	NULL
2	1	11	NULL
59 60 61 62
explain extended SELECT *, VALUES(a) FROM t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
Warnings:
63
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,values(`test`.`t1`.`a`) AS `VALUES(a)` from `test`.`t1`
64 65 66 67
explain extended select * from t1 where values(a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
Warnings:
68
Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1`
69
DROP TABLE t1;
vva@eagle.mysql.r18.ru's avatar
vva@eagle.mysql.r18.ru committed
70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
create table t1(a int primary key, b int);
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
select * from t1;
a	b
1	1
2	2
3	3
4	4
5	5
insert into t1 values(4,14),(5,15),(6,16),(7,17),(8,18)
on duplicate key update b=b+10;
affected rows: 7
info: Records: 5  Duplicates: 2  Warnings: 0
select * from t1;
a	b
1	1
2	2
3	3
4	14
5	15
6	16
7	17
8	18
replace into t1 values(5,25),(6,26),(7,27),(8,28),(9,29);
affected rows: 9
info: Records: 5  Duplicates: 4  Warnings: 0
select * from t1;
a	b
1	1
2	2
3	3
4	14
5	25
6	26
7	27
8	28
9	29
drop table t1;
108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142
CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B));
INSERT t1 VALUES (1,2,10), (3,4,20);
INSERT t1 SELECT 5,6,30 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	20
5	6	30
INSERT t1 SELECT 5,7,40 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	20
5	6	130
INSERT t1 SELECT 8,4,50 FROM DUAL ON DUPLICATE KEY UPDATE c=c+1000;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	1020
5	6	130
INSERT t1 SELECT 1,4,60 FROM DUAL ON DUPLICATE KEY UPDATE c=c+10000;
SELECT * FROM t1;
a	b	c
1	2	10010
3	4	1020
5	6	130
INSERT t1 SELECT 1,9,70 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100000, b=4;
ERROR 23000: Duplicate entry '4' for key 2
SELECT * FROM t1;
a	b	c
1	2	10010
3	4	1020
5	6	130
TRUNCATE TABLE t1;
INSERT t1 VALUES (1,2,10), (3,4,20);
143
CREATE TABLE t2 (a INT, b INT, c INT, d INT);
144 145
INSERT t2 VALUES (5,6,30,1), (7,4,40,1), (8,9,60,1);
INSERT t2 VALUES (2,1,11,2), (7,4,40,2);
146
INSERT t1 SELECT a,b,c FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=c+100;
147 148 149 150 151 152 153 154 155 156 157 158 159
SELECT * FROM t1;
a	b	c
1	2	10
3	4	120
5	6	30
8	9	60
INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
SELECT * FROM t1;
a	b	c
1	2	10
3	4	120
5	0	30
8	9	60
160
INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a);
161 162 163 164 165 166 167 168 169
SELECT *, VALUES(a) FROM t1;
a	b	c	VALUES(a)
1	2	10	NULL
3	4	127	NULL
5	0	30	NULL
8	9	60	NULL
2	1	11	NULL
DROP TABLE t1;
DROP TABLE t2;
170 171 172 173 174 175 176 177 178 179 180 181 182
create table t1 (a int not null unique);
insert into t1 values (1),(2);
insert ignore into t1 select 1 on duplicate key update a=2;
select * from t1;
a
1
2
insert ignore into t1 select a from t1 on duplicate key update a=a+1 ;
select * from t1;
a
1
3
drop table t1;