insertOnDuplicate isn't working in Magento

magento, mysql, php

Solution

In order to make the database understand a duplicate row to mean a row with a `test_type` the same as in an existing row, you could add `test_type` as a column in a unique index, e.g.

UNIQUE KEY `test_type_idx` (`test_type`)

thus changing your table creation code to be;

<?php

$installer = $this;
$installer->startSetup();
$installer->run("
DROP TABLE IF EXISTS {$installer->getTable('test_table')};
CREATE TABLE `{$installer->getTable('test_table')}` (
  `auto_id` int(10) NOT NULL auto_increment,
  `test_type` varchar(50) NOT NULL,
  `time_stamp` varchar(50) NOT NULL,
  PRIMARY KEY  (`auto_id`)
  UNIQUE KEY `test_type_idx` (`test_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

");
$installer->endSetup();

EDIT

The other issue is with the third parameter being passed to the `insertOnDuplicate` method. This should specify the fields which need to be updated if there is a duplicate, so presumably `time_stamp`.

$adapter->insertOnDuplicate('test_table', $data, array('time_stamp'));

Problem

So I'm trying to check custom database table, if 'something' exists in column 'test_type', replace the existing row with new data. However, it's still continuing writing duplicate column value 'test_type' in the database table. So I end up having two rows of 'something' in 'test_type' column. ``` $adapter = Mage::getSingleton('core/resource')->getConnection('core_write'); $data = array( 'test_type' => 'something', 'time_stamp' => $timeStamp, ); $adapter->insertOnDuplicate('test_table', $data, array('test_type')); ``` Here is the update script for creating the table. ``` <?php $installer = $this; $installer->startSetup(); $installer->run(" DROP TABLE IF EXISTS {$installer->getTable('test_table')}; CREATE TABLE `{$installer->getTable('test_table')}` ( `auto_id` int(10) NOT NULL auto_increment, `test_type` varchar(50) NOT NULL, `time_stamp` varchar(50) NOT NULL, PRIMARY KEY (`auto_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; "); $installer->endSetup(); ```

Original source