Many web applications require the developer to add data, if none is there, but amend data if some exists.

Take the example of an online survey. The user answers a question, and in our SQL we use INSERT to add the data to MySQL.

The first time a record is added we could use ‘auto-increment’ in MySQL to add a primary key.

The user now wants to edit that value – so in our SQL we would use UPDATE.

The UPDATE statement would need to know the primary key of that record.

These two tasks can be managed by one set of SQL. Here we can deploy the SQL command DUPLICATE to spot a duplicate record, and then use UPDATE rather than INSERT.

INSERT INTO surveyResults(id, answer2) VALUES ('Brill', 'Poor')
ON DUPLICATE KEY UPDATE answer = 'Brill';

In the above code sample the ‘id’ is the ‘surveyResults’ primary key. If no duplicate ‘id’ value found then the INSERT statement runs, if a duplicate is spotted then the UPDATE statement runs.

Leave a Comment