🚀 go-pugleaf

I guess I find the manuals "wanting".

In Sybase I can just do an insert, duplicates are ignored. In IB I can't as
you pointed out.

Someone replied saying the way to do this in IB is...

  if (exists(select * from table where myuniquekey = :keyparam)) then
    update table set ...
  else
    insert into table ...;

In the IB manual I have (yes, its old version 4.0). it says that there is a
keyword "EXISTS", but gives no further information, no samples, examples,
nothing. In Sybase I've never seen this "style" before.

The above code snippet is exactly what I'm looking for. This is the way IB
would handle the situation.

Another example is the following: I have a table I'll call DEST. It has in
int ID field and a VarChar PName field. I have an ID and I want to insert
the PName from another table called SOURCE that consists of an ID and a
PNAME. In Sybase I would do this

Update DEST set d.PName = s.PName  from DEST d, SOURCE s where d.ID = s.ID

You can't do this in IB. The manual says I should do the following

Update DEST set PName = (Select PName from SOURCE where SOURCE.ID = DEST.ID)

It works, but it's slower then HECK.

After digging around Deja.com I found the following to be a better way

for select ID, PName from SOURCE into :ID, :PName do begin update DEST set
PName = :PName where ID = :ID; end

A lot better. While doing more research I found that there still might be a
better way using "RDB$DB_KEY". I haven't played around with this techique
yet.

Maybe the problem is that I'm coming from a Sybase environment. I guess
Sybase doesn't do things in a "SQL-92 standard" way. I haven't found any
SQL-92 standard books either. They all seem to be Sybase, Oracle, SqlServer.

I'll keep looking, thanks for the reply.

Bradley

"Bill Todd" <bill@notthis.dbginc.com> wrote in message
news:82v80v8k2rn4e05smr4pgqi56bi4lu37so@4ax.com...
> What is it about SQL syntax that you cannot find in the InterBase
> manuals?
>
> In IB if an index is unique then you cannot insert a row with the same
> value as an existing row. For rows that exist you must use an UPDATE.
>
> On Sat, 21 Dec 2002 01:39:55 -0600, "Bradley J. Bell"
> <bradleyjbell@attb3i.c1o2m> wrote:
>
> >I currently use Sybase at work and I am use to its SQL syntax. I've
recently
> >decided to do some "brushing up" with Delphi and have decided to try
> >Interbase as the database.
> >
> >I'm having one heck of a time trying to learn Interabase's SQL syntax,
> >Interbase's "way" of doing things. I have been unable to find one
Interbase
> >programming book. What little I've learned is from searching Deja.com,
but
> >it takes a long time just to learn how to do simple things.
> >
> >Can anyone point me to a good book or website that teaches Interbase
> >"style"?
> >
> >TIA
> >
> >Bradley
> >
> >p.s. Just as a second question; in Sybase I can insert new records into a
> >database with a unique key and not worry about duplicates by including
"with
> >ignore dups" when defining the index. Does Interbase have a similar
option.
> >Right now what I've been doing is to take all the possible new entries
from
> >my source table and deleting any entries that already exisit in the
> >destination table. There has to be a better way (I hope).
> >
> >Again, TIA
> >
>
>
> --
> Bill (TeamB)
> (TeamB cannot respond to questions received via email)