How Can I Efficiently Insert or Update Data in MySQL Using a Single Query?

Streamlining MySQL Data Handling: Single-Query Inserts and Updates

MySQL offers an elegant solution for efficiently inserting new data or updating existing records based on key existence: the INSERT ... ON DUPLICATE KEY UPDATE statement.

Implementation Steps:

Begin with a standard INSERT statement, targeting the specific table and columns.

Append the ON DUPLICATE KEY UPDATE clause. This clause dictates the update actions triggered when a duplicate key (defined by a unique constraint or primary key) is detected.

Within the ON DUPLICATE KEY UPDATE section, define the columns to be updated and their new values.

Illustrative Example:

Consider a table named usage with columns thing_id, times_used, and first_time_used. To insert a new row for a given thing_id if it's absent, or increment times_used if the thing_id already exists, use this query:

INSERT INTO `usage` (`thing_id`, `times_used`, `first_time_used`)
VALUES (4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE `times_used` = `times_used` + 1;

The above is the detailed content of How Can I Efficiently Insert or Update Data in MySQL Using a Single Query?. For more information, please follow other related articles on the PHP Chinese website!