How Can I Efficiently Insert or Update Data Based on Two Conditions in SQL?

Insert or Update with Two Conditions

This problem appears straightforward; however, it necessitates finding an efficient time-wise solution. Consider a table with the following schema:

ID INTEGER PRIMARY KEY AUTOINCREMENT
name INTEGER
values1 INTEGER
values2 INTEGER
dates DATE

Each day, data is added to this table, resulting in new rows with future dates. The data in the 'name' column is drawn from a finite set. The desired behavior is to insert a new row if new data is available and update the existing row if a row with matching 'name' and 'dates' already exists.

Solution:

The appropriate method for this situation is the "INSERT INTO ... ON DUPLICATE KEY UPDATE" statement. This statement allows you to both insert new rows and update existing rows meeting specific conditions. In your case, the conditions are matching 'name' and 'dates'.

To use this method, your table must have a unique key (which can be a composite key) to detect the collision during insertion. This unique key can be the primary key or a composite key like this:

UNIQUE KEY(name, dates)

If a row with the same 'name' and 'date' already exists, the insert operation is detected as a conflict, and the update action specified in the "ON DUPLICATE KEY UPDATE" clause is performed.

Complete Example:

CREATE TABLE myThing (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name INT NOT NULL,
    values1 INT NOT NULL,
    values2 INT NOT NULL,
    dates DATE NOT NULL,
    UNIQUE KEY(name, dates)  -- This line is crucial
);

-- Insert a new row or update if a row with the same 'name' and 'date' exists
INSERT INTO myThing(name, values1, values2, dates) VALUES (777, 1, 1, '2015-07-11') ON DUPLICATE KEY UPDATE values2=values2+1;

By utilizing the "INSERT INTO ... ON DUPLICATE KEY UPDATE" statement, you can efficiently insert new rows or update existing rows based on the uniqueness of the specified key.

The above is the detailed content of How Can I Efficiently Insert or Update Data Based on Two Conditions in SQL?. For more information, please follow other related articles on the PHP Chinese website!