How to Update or Insert Rows in MySQL Based on Field Presence?

MySQL: Updating or Inserting Based on Field Presence

Problem Statement

In your code, you're aiming to update rows in the set_colors table if they already exist. Otherwise, insert new rows. However, you're utilizing the IF EXISTS conditional within the query, which doesn't align with the preferred way to handle such scenarios.

Solution

To perform the desired update or insert operation based on field presence, consider using the INSERT ... ON DUPLICATE KEY UPDATE syntax:

INSERT INTO <table name> (field1, field2, field3, ...) 
VALUES ('value1', 'value2', 'value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...

Explanation

With this syntax:

• If the row doesn't exist with the specified primary key (or unique index), it will be inserted with the provided values.
• If the row already exists, the specified fields will be updated to the provided values.
• If the row exists and the provided values are identical to the current ones, no action will be taken, and the affected row count will be 0.

Example

The following query demonstrates the usage:

INSERT INTO set_colors (school_art_id, baseimage_id, sub_folder, layer)
VALUES ('1', '2', 'test-folder', '10')
ON DUPLICATE KEY UPDATE
baseimage_id='2',
sub_folder='updated-folder',
layer='15'

• If school_art_id 1 doesn't exist, it will insert a new row with the provided values.
• If school_art_id 1 already exists, it will update the baseimage_id, sub_folder, and layer fields to the provided values.

The above is the detailed content of How to Update or Insert Rows in MySQL Based on Field Presence?. For more information, please follow other related articles on the PHP Chinese website!