ON DUPLICATE KEY UPDATE is non-destructive, in that it will only ever issue INSERT or UPDATE statements, but never DELETE.įor example, we have decided we wish to replace our id = 1 record of Green Eggs and Ham and revert it back to the original In Search of Lost Time record instead. Unlike REPLACE – an inherently destructive command due to the DELETE commands it performs when necessary – using INSERT. By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1. Your insert query is having id, if your table is not having any record with id 1 which is your table's primary key then it will work correct. ON DUPLICATE KEY UPDATE statement and clause. MySQL uses the AUTOINCREMENT keyword to perform an auto-increment feature. INSERT INTO users (id, name, email, pass, regtime) VALUES (null, 'user', '', 'pass', null) This is answer is for specific version of mysql. The alternative (and generally preferred) method for INSERTING into rows that may contain duplicate UNIQUE or PRIMARY KEY values is to use the INSERT. More information on using REPLACE can be found in the official documentation. Notice that even though we only altered one row, the result indicates that two rows were affected because we actually DELETED the existing row then INSERTED the new row to replace it. Seuss', 1960 ) Query OK, 2 rows affected ( 0. Mysql > REPLACE INTO books ( id, title, author, year_published ) VALUES ( 1, 'Green Eggs and Ham', 'Dr. The obvious purpose is to execute a large number of INSERT statements for a combination of data that is both already existing in the database as well as new data coming into the system.įor example, our books table might contain a few records already: This means that an INSERT IGNORE statement which contains a duplicate value in a UNIQUE index or PRIMARY KEY field does not produce an error, but will instead simply ignore that particular INSERT command entirely. Using INSERT IGNORE effectively causes MySQL to ignore execution errors while attempting to perform INSERT statements. MySQL provides a number of useful statements when it is necessary to INSERT rows after determining whether that row is, in fact, new or already exists.īelow we’ll examine the three different methods and explain the pros and cons of each in turn so you have a firm grasp on how to configure your own statements when providing new or potentially existing data for INSERTION.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |