45
45
REPLACE INTO t1 VALUE (2, "I love testing.");
50
# Test when using replace on table with multiple unique keys
51
# and a primary key of various data types. This tests code
52
# that, during a REPLACE, deletes multiple conflicting rows.
53
# (FYI, In the transaction log, there should be 2 DELETEs
54
# and 1 UPDATE for each of these REPLACE statements.)
57
create table t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
58
insert into t1 values (1,"a",1,1),(2,"b",2,2),(3,"c",3,3);
59
replace into t1 values (1,"b",3,4);
60
select * from t1 order by a;
63
create table t1 (a CHAR(5) NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
64
insert into t1 values ("a","a",1,1),("bb","b",2,2),("ccc","c",3,3);
65
replace into t1 values ("a","b",3,4);
66
select * from t1 order by a;
69
create table t1 (a DATE NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
70
insert into t1 values ("2010-01-01","a",1,1),("2010-02-02","b",2,2),("2010-03-03","c",3,3);
71
replace into t1 values ("2010-01-01","b",3,4);
72
select * from t1 order by a;
75
create table t1 (a DOUBLE NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
76
insert into t1 values (1.1,"a",1,1),(22.22,"b",2,2),(333.333,"c",3,3);
77
replace into t1 values (1.1,"b",3,4);
78
select * from t1 order by a;
81
create table t1 (a FLOAT NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
82
insert into t1 values (1.1,"a",1,1),(22.22,"b",2,2),(333.333,"c",3,3);
83
replace into t1 values (1.1,"b",3,4);
84
select * from t1 order by a;
87
create table t1 (a ENUM("a","bb","ccc") NOT NULL PRIMARY KEY, b char(1), c INT, d INT, unique key(b), unique key(c));
88
insert into t1 values ("a","a",1,1),("bb","b",2,2),("ccc","c",3,3);
89
replace into t1 values ("a","b",3,4);
90
select * from t1 order by a;