ON DUPLICATE KEY UPDATE syntax doesn't replicate to my slave servers. This is with server version 5.0.36sp1-enterprise-gpl-log. I wouldn't recommend anyone use the INSERT ... If you want to INSERT multiple records using single statement in MS SQL Server, then the syntax for My SQL wont work.
But you can use this insert command to accomplish the same: INSERT INTO tbl_test (First Name) SELECT ' Aleem' UNION ALL SELECT ' Latif' UNION ALL SELECT ' Mughal' With PHP, I use affected_rows to detect the success of an INSERT IGNORE.
Therefore, it is NOT recommended to use Insert-Select statements with Cluster databases if the auto-incrementing ID is meant to imply an absolute order on the timing of insertion into a table.
The developer will need to explicitly pull out each row from t2 and insert them individually into t1 for the desired effect.
When using INSERT IGNORE, insert triggers are STILL FIRED when a duplicate key constraint prevents new rows from being inserted.
The affected-rows value for an PDF (US Ltr) - 38.0Mb PDF (A4) - 38.1Mb PDF (RPM) - 36.9Mb HTML Download (TGZ) - 10.0Mb HTML Download (Zip) - 10.1Mb HTML Download (RPM) - 8.8Mb Man Pages (TGZ) - 206.8Kb Man Pages (Zip) - 315.3Kb Info (Gzip) - 3.5Mb Info (Zip) - 3.5Mb My SQL Backup and Recovery My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication Using the My SQL Yum Repository My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows My SQL NDB Cluster 7.5 My SQL 5.7 Secure Deployment Guide To insert special characters, like the "apostrophe" read the section on string syntax: an example: insert into Citylist (cityname) VALUES (' St. ON DUPLICATE KEY UPDATE..." can also use a compound (unique) key to check for duplication. For example: If you have a log table to log hits to different websites daily, with "site_id"-s and "time" fields, where neither of them are primary keys, but togethether they are unique, then you can create a key on them, and then use "...
ON DUPLICATE KEY..."Table logs:id: INT(11) auto_increment primary keysite_id: INT(11)time: DATEhits: INT(11)Then: CREATE UNIQUE INDEX comp ON logs (`site_id`, `time`); And then you can: INSERT INTO logs (`site_id`, `time`,`hits`) VALUES (1,"2004-08-09", 15) ON DUPLICATE KEY UPDATE hits=hits 15; Excellent feature, and it is much faster and briefer then using first a select, then issuing either an update or an insert depending on the value of the select.
For example, if we have two tables, t1 and t2: CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10)) TYPE=Inno DB; INSERT INTO t1 (b) VALUES (' Spike'), (' Chip'), (' John'); CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT NOT NULL, FOREIGN KEY (b) REFERENCES t1 (a), c VARCHAR(15)) TYPE=Inno DB; We can INSERT rows into t2 that populate the foreign key column based on a SELECT statement on t1: INSERT INTO t2 (b, c) VALUES ((SELECT a FROM t1 WHERE b=' Chip'), 'shoulder'), ((SELECT a FROM t1 WHERE b=' Chip'), 'old block'), ((SELECT a FROM t1 WHERE b=' John'), 'toilet'), ((SELECT a FROM t1 WHERE b=' John'), 'long,silver'), ((SELECT a FROM t1 WHERE b=' John'), 'li''l'); Then we get:mysql5 rows in set (0.00 sec)This is especially useful if you don't want to specify the ids for your rows (because they may differ from database to database, due to their being based on AUTO_INCREMENTs), but you want to refer to the values of other tables.
I haven't tested this to determine the version of My SQL this was introduced into, or whether it is necessary that the tables be Inno DB, but it works on my boxes (My SQL 4.1.12) If you want to add to a table A a column existing in a table B:1) Create an empty column in the table A: ALTER TABLE table A ADD color CHAR(20);2) If you don't have an auto-incrementation in the two tables (table B for exemple): ALTER TABLE table B ADD (id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id));3) Fill the columns with the values: UPDATE table A,table B SET table A.color=table B.color WHERE table A.id=table B.id; If you want to combine insert..select with setting an explicit value for a column - you can use join: INSERT INTO Target Table (col1, col2, col3)SELECT col1,col2,col3FROM Source Table JOIN (SELECT ' Explicit Value' AS col3) AS Any Alias This looks quite simple but it took me several hours to understand that there's no need for a special statement to handle such cases. JOIN (SELECT ' Explicit Value' AS col3) AS Any Alias You could easily do the same thing just by using: INSERT INTO Target Table (col1, col2, col3)SELECT col1,col2, ' Explicit Value' FROM Source Tablehth, Lokar I've just discovered that the UPDATE part of the INSERT ...