![]() Please note, I have left out all sql-injection prevention code for clarity. ![]() Let’s wrap this up with some quick PHP code to demonstrate one way to put a bulk Insert or Update query together, given an array of users. This comes in handy if each row will have a different value for non-key fields, as seen below: INSERT INTO user_preference (user_id, preference_id, value) INSERT INTO mytable (primaryid, count) VALUES(5, 1) ON DUPLICATE KEY UPDATE count count + 1 Assuming primaryid is defined as a primary key, in case the value. The Insert or Update syntax also allows you to use the VALUES() function to access the group of values that would be inserted had they not already existed. This is only the tip of the iceberg though. We can do that in a single query as seen in the example below: INSERT INTO user_preference (user_id, preference_id, pref_value) Let’s say we wanted to assign group of user’s a value of ‘true’ to preference_id 1. The user_preference table provides a many-to-many cross-reference table between the two. Assuming we have a users table and a preferences table. What if we face a situation where UPDATE or INSERT has. Let’s using the following cross-reference table as an example. Same goes with the UPDATE statement, but only if the query has more than one column we can fix the query. ![]() Okay I admit, I didn’t know until today!īy combining MySQL’s Bulk Insert syntax with the Insert or Update syntax, you are able to reduce a very large number of queries and statements down to one single statement. Many aren’t aware that this technique can also be applied in bulk queries. It’s essentially a way to leverage a database table’s primary key to reduce the number of queries required to insert or update a record, by eliminating the need to perform a select query first. If you’re not familiar with the MySQL Insert or Update statement, check it out before reading on.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |