Re: Insert Into Fails...

Actuall is was just a matter of specifying in my Insert Into clause ALL of the columns in the table I was inserting into. Hence: Begin Work; Lock Table root.udf In Exclusive Mode; Insert Into udf (udjoin, udtype, udfindex, udvalue, uddecimal, uddate, udsort) Select Distinct clnum, 'CL', 35, '', '95', '', 1 From client;Commit Work; "Jonathan Leffler" <jleffler@informix.com> wrote in message news:39DE3120.2BA78694@informix.com... > Steve Schroeder wrote: > > Because I have a Unique Index on the table I'm Inserting Into: > > > > The table has a unique index on: udtype, udjoin, udfindex, udsort > > > > Begin Work; > > Lock Table root.udf In Exclusive Mode; > > Insert Into udf Select Distinct clnum As udjoin, 'CL' As udtype, 35 As > > udfindex, '' As udvalue, '95' As uddecimal, '' As uddate, 1 As udsort From> > client; > > > > So, I get a message saying that the row cannot be added because it violates > > the Unique Index. I'm appending 14,000+ rows to my udf table. > > > > Can anyone tell me what I'm doing wrong here? Since my Select caluse > > included Distinct, that *should* make the each row unique right? > > There are several possibilities I can think of: > * Target table is not empty at start up > * The DISTINCT clause only ensures that all 7 columns together > are unique; your 4 column sub-set is repeated on occasion. > * You have managed to run across some sort of hash-collision in > the locking system. > > I rate the second choice the most likely. I did run into the lock > collision problem once upon a time about 7-8 years ago; the systems > have been changed and improved since then, so you should not be seeing > it. Of course, you don't say what version you are using... > > -- > Yours, > Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> > Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN > "I don't suffer from insanity; I enjoy every minute of it!"