Magento 2: How Insert on Duplicate works in this case?

Magento 2: How Insert on Duplicate works in this case?

August 29, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento 2: How Insert on Duplicate works in this case?

Managing Row Insertion and Updates Efficiently: Magento 2 and INSERT ON DUPLICATE

As a seasoned Magento developer, I often face scenarios where I need to manage the creation and updating of rows based on specific criteria within my database tables. In your case, you’re working with an ItemNo and GroupNo combination that requires either row creation or updating depending on whether the data already exists in the table.

Understanding the Scenario

Imagine you have a scenario like this:

ItemNo: 100 GroupNo: G05

You want to update the Qty field for the existing record if it exists, or create a new record with the specified ItemNo and GroupNo combination if it doesn’t exist. The goal is to avoid duplicate records while ensuring that your data remains consistent.

The Power of INSERT ON DUPLICATE

Magento 2 provides the ability to use MySQL’s INSERT ON DUPLICATE KEY UPDATE syntax to handle this scenario efficiently. This SQL feature allows you to insert a new row into a table (if it doesn’t exist) or update the existing row if the primary key or unique index already exists.

Building the SQL Query

Let’s construct the MySQL query that meets your requirements:

INSERT INTO my_table (ItemNo, GroupNo, Qty)
VALUES ('100', 'G05', 100)
ON DUPLICATE KEY UPDATE Qty = VALUES(Qty);

In this example:

  • Replace my_table with the actual name of your table.
  • The values '100' and 'G05' represent the ItemNo and GroupNo you want to work with.
  • The value 100 is the new quantity you want to set for either the creation or update operation.

The ON DUPLICATE KEY UPDATE clause specifies that if a duplicate key violation occurs (meaning a row already exists with the same ItemNo and GroupNo), then it should update the Qty field of the existing row with the value provided in the VALUES clause (VALUES(Qty)).

Incorporating It into Your Magento 2 Code

To execute this query within your Magento 2 module, you can use the Magento\Framework\DB\Adapter\Pdo\Mysql interface. Here’s a basic example of how to do it:

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$connection = $objectManager->create('Magento\Framework\DB\Adapter\Pdo\Mysql');
$sql = "INSERT INTO my_table (ItemNo, GroupNo, Qty) VALUES ('100', 'G05', 100) ON DUPLICATE KEY UPDATE Qty = VALUES(Qty)";
$connection->query($sql);

Conclusion

The INSERT ON DUPLICATE KEY UPDATE syntax is a powerful tool for developers working with Magento 2 databases. It allows you to manage the creation and updating of rows in an efficient manner, ensuring data consistency while avoiding duplicate records.

Remember that this is just one example of how to handle such scenarios. Depending on your specific requirements, you might need to adjust the query or use additional criteria to match your needs.

For more advanced database management techniques and best practices for Magento 2 development, I recommend visiting [https://laravelcompany.com/]. They offer a wealth of resources and guides that can help you master the intricacies of working with Magento’s database system.

Magento 2 Indexing Database Pricing

Our network