1--------------
2drop table if exists auto_incr_test,auto_incr_test2
3--------------
4
5Query OK, 0 rows affected
6
7--------------
8create table auto_incr_test (id int not null auto_increment, name char(40), timestamp timestamp, primary key (id))
9--------------
10
11Query OK, 0 rows affected
12
13--------------
14insert into auto_incr_test (name) values ("first record")
15--------------
16
17Query OK, 1 row affected
18
19--------------
20insert into auto_incr_test values (last_insert_id()+1,"second record",null)
21--------------
22
23Query OK, 1 row affected
24
25--------------
26insert into auto_incr_test (id,name) values (10,"tenth record")
27--------------
28
29Query OK, 1 row affected
30
31--------------
32insert into auto_incr_test values (0,"eleventh record",null)
33--------------
34
35Query OK, 1 row affected
36
37--------------
38insert into auto_incr_test values (last_insert_id()+1,"12","1997-01-01")
39--------------
40
41Query OK, 1 row affected
42
43--------------
44insert into auto_incr_test values (12,"this will not work",NULL)
45--------------
46
47ERROR 1062 at line 15: Duplicate entry '12' for key 1
48--------------
49replace into auto_incr_test values (12,"twelfth record",NULL)
50--------------
51
52Query OK, 2 rows affected
53
54--------------
55select * from auto_incr_test
56--------------
57
58id	name	timestamp
591	first record	19980817042654
602	second record	19980817042655
6110	tenth record	19980817042655
6211	eleventh record	19980817042655
6312	twelfth record	19980817042655
645 rows in set
65
66--------------
67create table auto_incr_test2 (id int not null auto_increment, name char(40), primary key (id))
68--------------
69
70Query OK, 0 rows affected
71
72--------------
73insert into auto_incr_test2 select NULL,name from auto_incr_test
74--------------
75
76Query OK, 5 rows affected
77Records: 5  Duplicates: 0  Warnings: 0
78
79--------------
80insert into auto_incr_test2 select id,name from auto_incr_test
81--------------
82
83Query OK, 3 rows affected
84Records: 5  Duplicates: 2  Warnings: 0
85
86--------------
87replace into auto_incr_test2 select id,name from auto_incr_test
88--------------
89
90Query OK, 5 rows affected
91Records: 5  Duplicates: 5  Warnings: 0
92
93--------------
94select * from auto_incr_test2
95--------------
96
97id	name
981	first record
992	second record
1003	tenth record
1014	eleventh record
1025	twelfth record
10310	tenth record
10411	eleventh record
10512	twelfth record
1068 rows in set
107
108--------------
109drop table auto_incr_test,auto_incr_test2
110--------------
111
112Query OK, 0 rows affected
113
114Bye
115