--disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings 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; INSERT t1 VALUES (5,7,40) ON DUPLICATE KEY UPDATE c=c+100; SELECT * FROM t1; INSERT t1 VALUES (8,4,50) ON DUPLICATE KEY UPDATE c=c+1000; SELECT * FROM t1; INSERT t1 VALUES (1,4,60) ON DUPLICATE KEY UPDATE c=c+10000; SELECT * FROM t1; -- error 1062 INSERT t1 VALUES (1,9,70) ON DUPLICATE KEY UPDATE c=c+100000, b=4; SELECT * FROM t1; 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; INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0; SELECT * FROM t1; INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a); SELECT *, VALUES(a) FROM t1; explain extended SELECT *, VALUES(a) FROM t1; explain extended select * from t1 where values(a); DROP TABLE t1; # # test for Bug #2709 "Affected Rows for ON DUPL.KEY undocumented, # perhaps illogical" # 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; --enable_info insert into t1 values(4,14),(5,15),(6,16),(7,17),(8,18) on duplicate key update b=b+10; --disable_info select * from t1; enable_info; replace into t1 values(5,25),(6,26),(7,27),(8,28),(9,29); disable_info; select * from t1; drop table t1; # WorkLog #2274 - enable INSERT .. SELECT .. UPDATE syntax # Same tests as beginning of this test except that insert source # is a result from a select statement # 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; INSERT t1 SELECT 5,7,40 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100; SELECT * FROM t1; INSERT t1 SELECT 8,4,50 FROM DUAL ON DUPLICATE KEY UPDATE c=c+1000; SELECT * FROM t1; INSERT t1 SELECT 1,4,60 FROM DUAL ON DUPLICATE KEY UPDATE c=c+10000; SELECT * FROM t1; -- error 1062 INSERT t1 SELECT 1,9,70 FROM DUAL ON DUPLICATE KEY UPDATE c=c+100000, b=4; SELECT * FROM t1; TRUNCATE TABLE t1; INSERT t1 VALUES (1,2,10), (3,4,20); CREATE TABLE t2 (x INT, y INT, z 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 x,y,z FROM t2 WHERE d=1 ON DUPLICATE KEY UPDATE c=c+100; SELECT * FROM t1; INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0; SELECT * FROM t1; INSERT t1 SELECT x,y,z FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a); SELECT *, VALUES(a) FROM t1; DROP TABLE t1; DROP TABLE t2;