Insert or update mysql7/30/2023 We need a unique key, and MySQL allows us to specify multiple columns via a composite key, which uniquely indentifies an entity occurrence. Now, when using INSERT on DUPLICATE KEY UPDATE, we need to specify the criteria that the database needs to check in order to decide if it should update or insert. Insert into users (listid,email,age,name) values that John, the same user, is inserted twice but we want to allow that as John belongs to a different list or listid. Let’s start with a simple mysql users table and insert some initial data with a multiple insert into statement. So, email by itself is not the only unique criteria for a user but the unique criteria is based on multiple columns. ![]() So, INSERT on DUPLICATE KEY UPDATE is most likely the optimal query but how do you do that for multiple rows? And, how do you do that based on multiple criteria for uniqueness? Maybe I want to allow the same emails in the table as they belong to a different list or a different user which are different columns in the table. ![]() But, it should probably be avoided as it actually performs a DELETE and INSERT operation and updates all indexes. REPLACE INTO seems like a great solution considering that the query has the same syntax as a normal INSERT INTO. This leaves you with a choice of using REPLACE INTO or INSERT on DUPLICATE KEY UPDATE. You want to offload such logic to the database and do it all with 1 query. ![]() It would not be optimal to write a loop and for each row, check if email exists (assuming this is the unique criteria for a user), and then insert or update. This is a very common scenario where you need to figure out if you should update or insert. Now you need to insert 10 more rows of data but you don’t know if the 10 new rows are for new users or if they are attributes for users you already have in the database. You have a table with some data, lets say it is user information with emails, names, ages, etc.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |