Re: SQL question, getting error and not sure why

with second sql i meant: (select 'Michael Sweeney' BB from acteursenc) if you want one row and you need a dummy table, use "dual". Michael "Michael Virnstein" <michael.virnstein@web.de> schrieb im Newsbeitrag news:20020530150953.28106.qmail@pb1.pair.com... > the problem is, that the second sql produces more > than one row. To be exactly the amount of > select count(*) from acteursenc rows, with > 'Michael Sweeney' as value in BB. Perhaps you > have a unique key on nomacteur?! > > I could help further, if i know what you want to do > with your query. > if nuacteur is a pk, use a sequence!!!!! > if two users send this query almost at the same time, > you'll get two times the same pk. > That's because of oracles locking mechanism: > Read does not block read. > read does not block write. > write does not block read. > write blocks write on the same column. > if someone inserts a row with your statement, and hasn't commited his > transaction, > and someone else inserts a row with your statment before he has commited, > then the two will get the same results for max(id)+1. A sequence will never > give the > same result and is easy to use. and for your query, wouldn' this be easier: > insert into acteursenc > (nuacteur, nomacteur) > values > (S_ACTEURSENC.NEXTVAL, 'Michael Sweeney') > > please explain what you want to do. > > Michael > > "Michael Sweeney" <mike_sweeney@videotron.ca> schrieb im Newsbeitrag > news:20020530144030.77571.qmail@pb1.pair.com... > > My following query : > > > > insert into acteursenc (nuacteur,nomacteur) > > (select AA, BB from > > (select max(nuacteur)+1 AA from acteursenc), > > (select 'Michael Sweeney' BB from acteursenc))" > > > > produces an ORA-00001: unique constraint error. > > > > The primary key is nuacteur, but by setting AA to max(nuacteur)+1 I should > > be getting a new key that is unique, however it does not seem that way. > > > > What am I doing wrong here? > > > > > >