well a few things catch my eye. use $ for the first parameters in the sql.
INSERT INTO MY_TABLE ( COL1, COL2) SELECT $col1$, $col2$ FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM MY_TABLE WHERE COL1 = #col1# AND COL2 = #col2#) and second, if you are using timestamps you might have to specify the jdbc type INSERT INTO MY_TABLE ( COL1, COL2) SELECT $col1$, $col2$ FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM MY_TABLE WHERE COL1 = #col1:TIMESTAMP# AND COL2 = #col2:TIMESTAMP#) The second will only for for timestamps now and not normal strings. Nathan On Nov 19, 2007 8:56 AM, MrNobody <[EMAIL PROTECTED]> wrote: > > I'm trying to do something where I insert rows only if certain parts of > the > data does not already exist. So I put a where not exists clause in my > insert > statement and it will work fine when I am comparing strings and/or numbers > however when I introduce Timestamps it begans to act funny. > > So basically I did something like this: > > INSERT INTO MY_TABLE ( COL1, COL2) > SELECT #col1#, #col2# FROM DUAL > WHERE NOT EXISTS > (SELECT 1 FROM MY_TABLE WHERE COL1 = #col1# AND COL2 = #col2#) > > When COL1 and COL2 are either strings and/or numbers this works as > expected, > however if one is a Timestamp it fails.It duplicates every row. > > I tried converting the Timestamps to strings like this: > > AND to_char(COL2, 'mm-dd-yyyy hh24:mi:ss') = to_char(#col2#, 'mm-dd-yyyy > hh24:mi:ss') > > and what happens now is only SOME rows get duplicated, not all of them > like > before. Very bizarre... > -- > View this message in context: > http://www.nabble.com/Inserting---not-exist-problem-with-date-compare-tf4837196.html#a13838678 > Sent from the iBATIS - User - Java mailing list archive at Nabble.com. > >
