ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nathan Maves" <nathan.ma...@gmail.com>
Subject Re: Inserting / not exist problem with date compare
Date Mon, 19 Nov 2007 20:59:04 GMT
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 <tony.lozina@pepsi.com> 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.
>
>

Mime
View raw message