![]() If a new row is added, the auto_increment is moved forward. 004 sec ) SELECT * FROM ins_duplicate + -+-+-+ | id | animal | id2 | + -+-+-+ | 1 | Antelope | 11 | | 2 | Lion | 12 | | 3 | Zebra | 13 | | 4 | Gorilla | 14 | + -+-+-+Īlthough the third row with an id of 3 has an id2 of 13, which also matched, it was not updated.Ĭhanging id to an auto_increment field. INSERT INTO ins_duplicate VALUES ( 2, 'Lion', 13 ) ON DUPLICATE KEY UPDATE animal = 'Lion' Query OK, 2 rows affected ( 0. ![]() This can be unsafe and is not recommended unless you are certain what you are doing. Where two rows match the unique keys match, only the first is updated. ![]() SELECT * FROM ins_duplicate + -+-+ | id | animal | + -+-+ | 1 | Antelope | | 2 | Cheetah | | 3 | Zebra | | 4 | Gorilla | + -+-+Īdding a second unique column: ALTER TABLE ins_duplicate ADD id2 INT UPDATE ins_duplicate SET id2 = id + 10 ALTER TABLE ins_duplicate ADD UNIQUE KEY ( id2 ) Note that there are two rows reported as affected, but this refers only to the UPDATE. However, we can use an INSERT ON DUPLICATE KEY UPDATE instead: INSERT INTO ins_duplicate VALUES ( 1, 'Antelope' ) ON DUPLICATE KEY UPDATE animal = 'Antelope' Query OK, 2 rows affected ( 0. 07 sec ) SELECT * FROM ins_duplicate + -+-+ | id | animal | + -+-+ | 1 | Aardvark | | 2 | Cheetah | | 3 | Zebra | | 4 | Gorilla | + -+-+Ī regular INSERT with a primary key value of 1 will fail, due to the existing key: INSERT INTO ins_duplicate VALUES ( 1, 'Antelope' ) ERROR 1062 ( 23000 ): Duplicate entry '1' for key 'PRIMARY' If there is no existing key, the statement runs as a regular INSERT: INSERT INTO ins_duplicate VALUES ( 4, 'Gorilla' ) ON DUPLICATE KEY UPDATE animal = 'Gorilla' Query OK, 1 row affected ( 0. Examples CREATE TABLE ins_duplicate ( id INT PRIMARY KEY, animal VARCHAR ( 30 )) INSERT INTO ins_duplicate VALUES ( 1, 'Aardvark' ), ( 2, 'Cheetah' ), ( 3, 'Zebra' ) This statement activates INSERT and UPDATE triggers. See Partition Pruning and Selection for details on the PARTITION clause. The IGNORE and DELAYED options are ignored when you use ON DUPLICATE KEY UPDATE. This function is particularly useful for multi-rows inserts. It returns the column values from the INSERT portion of the statement. The VALUES() function can only be used in a ON DUPLICATE KEY UPDATE clause and has no meaning in any other context. If the table has an AUTO_INCREMENT primary key and the statement inserts or updates a row, the LAST_INSERT_ID() function returns its AUTO_INCREMENT value. It is not recommended to use this statement on tables with more than one unique index. If more than one unique index is matched, only the first is updated. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set. ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. There is a great need for a sarcasm font.INSERT. I would assume SQLite also uses some kind of a sequence for PK Which hashid value do you use then?Īlso, Access uses something called 'AutoNumber' field as PK for a table, Oracle and SQL Server uses sequences to increment PK value in the tables. Your 'pseudocode' logic may theoretically work if you always insert a record into the emailprops table related to the last record in email table, but is that always the case? You may have 100 records in email table, and you want to insert a record into emailprops table related to the 27th record in email table. But how do you know which record in emailprops table relates to which record in email table before you insert the record into emailprops table? And which hashid value to use as the value for compid in emailprops table? I don't think you can have this logic in the definition of the tables, you need to have it in the code that deals with INSERT INTO emailprops statements. So in order to INSERT any record into emailprops table, you need to have a record in the email table. Well, you have here Parent (email) - Child (emailprops) relationship between the 2 tables.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |