Edit File: insert_update.result
DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (a INT, b INT, c INT, UNIQUE (A), UNIQUE(B)); INSERT t1 VALUES (1,2,10), (3,4,20); INSERT t1 VALUES (5,6,30) ON DUPLICATE KEY UPDATE c=c+100; SELECT * FROM t1; a b c 1 2 10 3 4 20 5 6 30 INSERT t1 VALUES (5,7,40) ON DUPLICATE KEY UPDATE c=c+100; SELECT * FROM t1; a b c 1 2 10 3 4 20 5 6 130 INSERT t1 VALUES (8,4,50) ON DUPLICATE KEY UPDATE c=c+1000; SELECT * FROM t1; a b c 1 2 10 3 4 1020 5 6 130 INSERT t1 VALUES (1,4,60) ON DUPLICATE KEY UPDATE c=c+10000; SELECT * FROM t1; a b c 1 2 10010 3 4 1020 5 6 130 INSERT t1 VALUES (1,9,70) ON DUPLICATE KEY UPDATE c=c+100000, b=4; ERROR 23000: Duplicate entry '4' for key 'b' 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); INSERT t1 VALUES (5,6,30), (7,4,40), (8,9,60) ON DUPLICATE KEY UPDATE c=c+100; 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 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 explain extended SELECT *, VALUES(a) FROM t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 100.00 Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,NULL AS `VALUES(a)` from `test`.`t1` explain extended select * from t1 where values(a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where 0 DROP TABLE t1; 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; 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 'b' 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); CREATE TABLE t2 (a INT, b INT, c INT, d INT); 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); INSERT t1 SELECT a,b,c FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=t1.c+100; 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 INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a); ERROR 23000: Column 'c' in field list is ambiguous INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.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 DROP TABLE t1; DROP TABLE t2; create table t1 (a int not null unique) engine=myisam; 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 as t2 on duplicate key update a=t1.a+1 ; select * from t1; a 1 3 insert into t1 select 1 on duplicate key update a=2; select * from t1; a 2 3 insert into t1 select a from t1 on duplicate key update a=a+1 ; ERROR 23000: Column 'a' in field list is ambiguous insert ignore into t1 select a from t1 on duplicate key update a=t1.a+1 ; ERROR 23000: Column 't1.a' in field list is ambiguous drop table t1; CREATE TABLE t1 ( a BIGINT(20) NOT NULL DEFAULT 0, PRIMARY KEY (a) ) ENGINE=MyISAM; INSERT INTO t1 ( a ) SELECT 0 ON DUPLICATE KEY UPDATE a = a + VALUES (a) ; DROP TABLE t1; CREATE TABLE t1 ( a BIGINT UNSIGNED, b BIGINT UNSIGNED, PRIMARY KEY (a) ); INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = IF(VALUES(b) > t1.b, VALUES(b), t1.b); SELECT * FROM t1; a b 45 1 INSERT INTO t1 VALUES (45, 2) ON DUPLICATE KEY UPDATE b = IF(VALUES(b) > t1.b, VALUES(b), t1.b); SELECT * FROM t1; a b 45 2 INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = IF(VALUES(b) > t1.b, VALUES(b), t1.b); SELECT * FROM t1; a b 45 2 DROP TABLE t1; CREATE TABLE t1 (i INT PRIMARY KEY, j INT); INSERT INTO t1 SELECT 1, j; ERROR 42S22: Unknown column 'j' in 'field list' DROP TABLE t1; CREATE TABLE t1 (i INT PRIMARY KEY, j INT); CREATE TABLE t2 (a INT, b INT); CREATE TABLE t3 (a INT, c INT); INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 ON DUPLICATE KEY UPDATE j= a; DROP TABLE t1,t2,t3; CREATE TABLE t1 (i INT PRIMARY KEY, j INT); CREATE TABLE t2 (a INT); INSERT INTO t1 VALUES (1, 1); INSERT INTO t2 VALUES (1), (3); INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a; ERROR 42S22: Unknown column 'a' in 'field list' DROP TABLE t1,t2; SET SQL_MODE = 'TRADITIONAL'; CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL); INSERT INTO t1 (a) VALUES (1); ERROR HY000: Field 'b' doesn't have a default value INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b; ERROR HY000: Field 'b' doesn't have a default value INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b; ERROR HY000: Field 'b' doesn't have a default value SELECT * FROM t1; a b DROP TABLE t1; CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY, f2 VARCHAR(5) NOT NULL UNIQUE); INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1); SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 1 INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1); SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 1 DROP TABLE t1; SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; CREATE TABLE `t1` ( `id` int(11) PRIMARY KEY auto_increment, `f1` varchar(10) NOT NULL UNIQUE ); INSERT IGNORE INTO t1 (f1) VALUES ("test1") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); INSERT IGNORE INTO t1 (f1) VALUES ("test1") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 1 SELECT * FROM t1; id f1 1 test1 INSERT IGNORE INTO t1 (f1) VALUES ("test2") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT * FROM t1; id f1 1 test1 2 test2 INSERT IGNORE INTO t1 (f1) VALUES ("test2") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 2 SELECT * FROM t1; id f1 1 test1 2 test2 INSERT IGNORE INTO t1 (f1) VALUES ("test3") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 3 SELECT * FROM t1; id f1 1 test1 2 test2 3 test3 DROP TABLE t1; CREATE TABLE `t1` ( `id` int(11) PRIMARY KEY auto_increment, `f1` varchar(10) NOT NULL UNIQUE ); INSERT IGNORE INTO t1 (f1) VALUES ("test1") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 1 SELECT * FROM t1; id f1 1 test1 INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4") ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id); SELECT LAST_INSERT_ID(); LAST_INSERT_ID() 2 SELECT * FROM t1; id f1 1 test1 2 test4 DROP TABLE t1; CREATE TABLE `t1` ( `id` int(11) PRIMARY KEY auto_increment, `f1` varchar(10) NOT NULL UNIQUE, tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp ); INSERT INTO t1 (f1) VALUES ("test1"); SELECT id, f1 FROM t1; id f1 1 test1 REPLACE INTO t1 VALUES (0,"test1",null); SELECT id, f1 FROM t1; id f1 0 test1 DROP TABLE t1; SET SQL_MODE=''; CREATE TABLE t1 ( id INT AUTO_INCREMENT PRIMARY KEY, c1 CHAR(1) UNIQUE KEY, cnt INT DEFAULT 1 ); INSERT INTO t1 (c1) VALUES ('A'), ('B'), ('C'); SELECT * FROM t1; id c1 cnt 1 A 1 2 B 1 3 C 1 INSERT INTO t1 (c1) VALUES ('A'), ('X'), ('Y'), ('Z') ON DUPLICATE KEY UPDATE cnt=cnt+1; SELECT * FROM t1; id c1 cnt 1 A 2 2 B 1 3 C 1 4 X 1 5 Y 1 6 Z 1 DROP TABLE t1; CREATE TABLE t1 ( id INT AUTO_INCREMENT PRIMARY KEY, c1 INT NOT NULL, cnt INT DEFAULT 1 ); INSERT INTO t1 (id,c1) VALUES (1,10); SELECT * FROM t1; id c1 cnt 1 10 1 CREATE TABLE t2 (id INT, c1 INT); INSERT INTO t2 VALUES (1,NULL), (2,2); INSERT INTO t1 (id,c1) SELECT 1,NULL ON DUPLICATE KEY UPDATE c1=NULL; ERROR 23000: Column 'c1' cannot be null SELECT * FROM t1; id c1 cnt 1 10 1 INSERT IGNORE INTO t1 (id,c1) SELECT 1,NULL ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1; Warnings: Warning 1048 Column 'c1' cannot be null Error 1048 Column 'c1' cannot be null SELECT * FROM t1; id c1 cnt 1 0 2 INSERT IGNORE INTO t1 (id,c1) SELECT * FROM t2 ON DUPLICATE KEY UPDATE c1=NULL, cnt=cnt+1; Warnings: Warning 1048 Column 'c1' cannot be null Error 1048 Column 'c1' cannot be null SELECT * FROM t1; id c1 cnt 1 0 3 2 2 1 DROP TABLE t1; DROP TABLE t2; create table t1(f1 int primary key, f2 timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP); insert into t1(f1) values(1); select @stamp1:=f2 from t1; @stamp1:=f2 # insert into t1(f1) values(1) on duplicate key update f1=1; select @stamp2:=f2 from t1; @stamp2:=f2 # select if( @stamp1 = @stamp2, "correct", "wrong"); if( @stamp1 = @stamp2, "correct", "wrong") correct drop table t1;
Back to File Manager