Re: "SQL" REPLACE SYNTAX

Hello,

Vince Vielhaber wrote:
>
> On 23 Jan 2002, Andrew McMillan wrote:
>
> > I realise this wanders far from the SQL standard, but it's one of those
> > areas where the standard is awkward from an application programmer's
> > point of view. I would certainly love to see some syntax of this kind
> > available. If having to work out the primary key from schema queries is
> > too much work, then why not have a syntax that explicitly coped - after
> > all the whole thing is non-standard, so we don't have to follow MySQL's
> > lead on it:
> >
> > INSERT OR REPLACE <table> ( <field list> )
> > VALUES( <value list> ) WHERE <condition>
> >
> > would be just fine by me.
> >
> > There are many places in my programs where I want to do this sort of
> > INSERT OR REPLACE functionality and the ability to do so would make my
> > code more maintainable, and much less prone to stupid errors.
> >
> > More simply (?) I want to be able to build the fields / values for my
> > query and then decide at the last minute whether I was doing an INSERT
> > or an UPDATE.
> >
> > To do this (without throwing an error) I currently do a "SELECT ...
> > WHERE <condition>;" and then construct my SQL appropriately. When I add
> > a new column to a table (this is the real world :-) I frequently forget
> > to maintain one branch of the resulting code building the SQL statement.
> >
> > It is this need that Manuel is catering for by implementing the
> > functionality in Metabase, but I expect it would be more efficient to
> > implement it in PostgreSQL natively.
>
> INSERT OR REPLACE table1(a,b,c) VALUES(1,2,3) WHERE a=3 and b=4 and c=5;

REPLACE only uses the primary key to figure if a row exists, so it won't
find more than one row.

> BTW, INSERT OR UPDATE seems more appropriate since you'd be doing an
> UPDATE if the row already exists. I think that's also what Oracle uses.

That's what Metabase uses as default implementation, it works in
PostgreSQL, Oracle, MS SQL server, Informix, Interbase and eventually
with any ODBC data source that supports SQL and transactions.

Regards,
Manuel Lemos