diff options
Diffstat (limited to 'mysql-test/r/insert_update.result')
| -rw-r--r-- | mysql-test/r/insert_update.result | 62 |
1 files changed, 62 insertions, 0 deletions
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index 5db36cf6a66..7027d7eff79 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -105,3 +105,65 @@ a b 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 2 +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 (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; +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 x,y,z FROM t2 WHERE d=2 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 +DROP TABLE t1; +DROP TABLE t2; |