Error in update[TCX][MyODBC]Duplicate entry '20020128153002' for key 1

[Guest_imported]

I get an error ([TCX][MyODBC]Duplicate entry '20020128153002' for key 1) when trying to do this:

update transactions
set price = 200
where transtime >= '2001-01-01'
and transtime <= '2002-01-27'
and customerid = 'moonwalk';

The table definition is:

create table TRANSACTIONS (
TRANSACTIONID timestamp not null,
CUSTOMERID varchar(15) not null,
USERID int,
TRANSTIME date not null,
PRICE int not null,
SEARCHTYPEID int not null,
GENINVOICEID int,
primary key (TRANSACTIONID));

Thats wrong??

 

[Broccoli2]

Not quite sure, but it may be to do with setting your

TRANSACTIONID

field as

not null

. The error indicates that you have a duplicate entry for your timestamp field. Change your table definition, then see if you get the same error.

-Rob

 

[petersJazz]

Something strange about using timestamp as a primary key. Changed to transactionid varchar(20) and it worked.