[solved] INSERT INTO .. ON DUPLICATE KEY UPDATE

[sean]

Hi,

I have a Tmyquery component with the following Update SQL:

INSERT INTO clients
SET account = :account, inv_conformance = :inv_conformance, note = :note
ON DUPLICATE KEY UPDATE
inv_conformance = :inv_conformance, note = :note

The idea is to update fields if they have changed, or insert a new row if none exists.

Inserting new rows work, but when changing a row /field that exists the error:
"Update failed found two records"
appears.
However it has actually update the record correctly.

Doing such a commands from the mysql command line does work, although it say two rows affected:

mysql> INSERT INTO clients
-> SET account = 2707, inv_conformance = 1, note = 'hello'
-> ON DUPLICATE KEY UPDATE
-> inv_conformance = 1, note = 'hello';;
Query OK, 2 rows affected (0.00 sec)

The table schema is:

Code: Select all

CREATE TABLE `clients` (
  `id` int(11) NOT NULL auto_increment,
  `account` char(15) default NULL,
  `inv_conformance` int(11) default '0',
  `note` char(50) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `account` (`account`)
) 

D7/5.90.0.52

Thanks in advance.

[Dimon]

To solve the problem try to set the TMyQuery.Options.StrictUpdate property to False.

[sean]

Excellent, that worked. Thanks.