236
236
INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a;
237
237
ERROR 42S22: Unknown column 'a' in 'field list'
238
238
DROP TABLE t1,t2;
239
SET SQL_MODE = 'TRADITIONAL';
240
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL);
241
INSERT INTO t1 (a) VALUES (1);
242
ERROR HY000: Field 'b' doesn't have a default value
243
INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE a = b;
244
ERROR HY000: Field 'b' doesn't have a default value
245
INSERT INTO t1 (a) VALUES (1) ON DUPLICATE KEY UPDATE b = b;
246
ERROR HY000: Field 'b' doesn't have a default value
250
CREATE TABLE t1 (f1 INT AUTO_INCREMENT PRIMARY KEY,
251
f2 VARCHAR(5) NOT NULL UNIQUE);
252
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
253
SELECT LAST_INSERT_ID();
256
INSERT t1 (f2) VALUES ('test') ON DUPLICATE KEY UPDATE f1 = LAST_INSERT_ID(f1);
257
SELECT LAST_INSERT_ID();
261
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
263
`id` int(11) PRIMARY KEY auto_increment,
264
`f1` varchar(10) NOT NULL UNIQUE
266
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
267
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
268
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
269
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
270
SELECT LAST_INSERT_ID();
276
INSERT IGNORE INTO t1 (f1) VALUES ("test2")
277
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
282
INSERT IGNORE INTO t1 (f1) VALUES ("test2")
283
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
284
SELECT LAST_INSERT_ID();
291
INSERT IGNORE INTO t1 (f1) VALUES ("test3")
292
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
293
SELECT LAST_INSERT_ID();
303
`id` int(11) PRIMARY KEY auto_increment,
304
`f1` varchar(10) NOT NULL UNIQUE
306
INSERT IGNORE INTO t1 (f1) VALUES ("test1")
307
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
308
SELECT LAST_INSERT_ID();
314
INSERT IGNORE INTO t1 (f1) VALUES ("test1"),("test4")
315
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
316
SELECT LAST_INSERT_ID();
325
`id` int(11) PRIMARY KEY auto_increment,
326
`f1` varchar(10) NOT NULL UNIQUE,
327
tim1 timestamp default '2003-01-01 00:00:00' on update current_timestamp
329
INSERT INTO t1 (f1) VALUES ("test1");
330
SELECT id, f1 FROM t1;
333
REPLACE INTO t1 VALUES (0,"test1",null);
334
SELECT id, f1 FROM t1;