forked from MariaDB/server
/
insert_returning_test1.test
73 lines (66 loc) · 2.73 KB
/
insert_returning_test1.test
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
#
# Test for INSERT...RETURNING
#
--disable_warnings
drop table if exists t1;
--enable_warnings
CREATE TABLE t1(id1 INT, id2 INT, val VARCHAR(1));
--echo Table t1 created successfully. Fields: id1 INT, id2 INT, val VARCHAR(1);
#
#Simple insert statement
#
--echo #Simple insert statement
INSERT INTO t1 (id1, id2, val) VALUES (1, 2, 'a');
INSERT INTO t1 (id1, id2, val) VALUES (2, 3, 'b') RETURNING *;
INSERT INTO t1 (id1, id2, val) VALUES (3, 4, 'c') RETURNING id1, val;
INSERT INTO t1 (id1, id2, val) VALUES (4, 5, 'd') RETURNING id1 AS id;
INSERT INTO t1 (id1, id2, val) VALUES (5, 6, 'e') RETURNING id1+id2 AS total;
INSERT INTO t1 (id1, id2, val) VALUES (6, 7, 'f') RETURNING id1 & id2;
INSERT INTO t1 (id1, id2, val) VALUES (7, 8, 'g') RETURNING id1 || id2;
SELECT * FROM t1;
TRUNCATE TABLE t1;
SELECT * FROM t1;
#
#multiple values in one insert statement
#
--echo #multiple values in one insert statement
INSERT INTO t1 VALUES (1, 2, 'a'),(2, 3, 'b');
INSERT INTO t1 VALUES (3, 4, 'c'),(4, 5, 'd') RETURNING *;
INSERT INTO t1 VALUES (5, 6, 'e'),(6, 7, 'f') RETURNING id2, val;
INSERT INTO t1 VALUES (7, 8, 'g'),(8, 9, 'h') RETURNING id2 AS id;
INSERT INTO t1 VALUES (9, 10, 'i'),(10, 11, 'j') RETURNING id2+id1 as total;
INSERT INTO t1 VALUES (11, 12, 'k'),(12, 13, 'l') RETURNING id2 & id1;
INSERT INTO t1 VALUES (5, 6, 'e'),(2, 3, 'f') RETURNING id2 || id1;
SELECT * FROM t1;
TRUNCATE TABLE t1;
#
#INSERT...ON DULPICATE KEY UPDATE
#
--echo # INSERT...ON DULPICATE KEY UPDATE
CREATE TABLE ins_duplicate (id1 INT PRIMARY KEY, id2 INT, val VARCHAR(1));
INSERT INTO ins_duplicate VALUES (1,2,'a');
INSERT INTO ins_duplicate VALUES (2,3,'b') ON DUPLICATE KEY UPDATE val='b' RETURNING id1,val;
INSERT INTO ins_duplicate VALUES (2,4,'b') ON DUPLICATE KEY UPDATE val='c' RETURNING *;
INSERT INTO ins_duplicate VALUES (2,5,'b') ON DUPLICATE KEY UPDATE val='d' RETURNING id1+id2 AS total;
INSERT INTO ins_duplicate VALUES (2,6,'b') ON DUPLICATE KEY UPDATE val='e' RETURNING id1 || id2;
INSERT INTO ins_duplicate VALUES (2,7,'b') ON DUPLICATE KEY UPDATE val='f' RETURNING id1 & id2;
INSERT INTO ins_duplicate VALUES (2,8,'b') ON DUPLICATE KEY UPDATE val='g' RETURNING id1 AS id;
SELECT * FROM ins_duplicate;
#
# INSERT...SET
#
--echo # INSERT...SET
INSERT INTO t1 SET id1=1, id2=2, val='a';
INSERT INTO t1 SET id1=2, id2=3, val='b' RETURNING *;
INSERT INTO t1 SET id1=3, id2=4, val='c' RETURNING val;
INSERT INTO t1 SET id1=4, id2=5, val='d' RETURNING id1+id2 AS total;
INSERT INTO t1 SET id1=5, id2=6, val='e' RETURNING id1 & id2;
INSERT INTO t1 SET id1=6, id2=7, val='f' RETURNING id1 || id2;
INSERT INTO t1 SET id1=7, id2=8, val='g' RETURNING val as letter;
SELECT * FROM t1;
--echo Droping t1 and ins_duplicate
DROP TABLE t1;
DROP TABLE ins_duplicate;
#
--echo #End of test case
#