Less queries with INSERT ... ON DUPLICATE KEY UPDATE

And 'well-known that a correct query management is a prerequisite for stable and efficient implementation.
In this article we will present a little known but highly powerful syntax, I'm talking about the ON DUPLICATE KEY UPDATE clause.

This clause is used during an INSERT statement and its purpose is to verify, before insertion, there is a duplication of a primary key (primary key) or a unique key (UNIQUE KEY), and if this happens the engine un'UPDATE MySQL will instead insert.

The advantage of this clause is clear: you write a single query instead of two with advantages in terms of performance and code cleanup.

Let's take an example. Suppose we want to make a simple script that performs the logging of visitors to our site. Below the table structure of our DB:

• IP (key)
• numero_visite
• ultima_visita

Wanting to record every IP in transit on our pages we have, according to the usual logic, do a SELECT first preliminary check that the IP is not already in the DB and only if the answer is negative (IP not present) to query INSERT.

Thanks to the syntax INSERT ... ON DUPLICATE KEY UPDATE ... we can achieve the same result with a single query:

 



 INSERT INTO ip_visitatori VALUES ('123 .123.123.123 ', 1, NOW ())

 





 ON KEY DPLICATE







 UPDATE numero_visite numero_visite = + 1, ultima_visita = NOW ();

 

Thus, if the IP is the inclusion is made, otherwise you run a simple update of the affected record. Using this syntax allows to obtain a benefit in terms of performance equal to 30%.

With a little 'imagination and ingenuity of the clause in question there can be very useful in various circumstances.
For example we can use it in combination with a condition. Here's an example: Suppose you have a table of a hypothetical online auction site structured as follows:

• ID_asta
• migliore_offerta

Suppose you want to proceed with the placement of a new offer, if the auction has already offered there will be a simple upgrade, but only if the offer is higher than the one already there:

 



 Offers INSERT INTO VALUES (1, 120)

 





 ON KEY DPLICATE







 UPDATE migliore_offerta = IF (VALUES (migliore_offerta) <120, 120, VALUES (migliore_offerta))

 

Through a simple query we solved in one fell swoop ... Otherwise we would have had to use several queries with a consequent waste of resources (and a greater risk of errors).