Re: "SQL" REPLACE SYNTAX - Mailing list pgsql-php

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;

On the surface it looks ok.  But..  What happens if you have a table
that isn't just a,b,c there are actually a,b,c,d,e,f and there are more
than one tuple with a=3 and b=4 and c=5?  Do you replace them all or
throw an error?  Isn't this what Chris was eluding to?

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.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
         56K Nationwide Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================