Re: "SQL" REPLACE SYNTAX

On Thu, 2002-01-24 at 00:24, Vince Vielhaber wrote:
>
> 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?

OK, so the implementor would have to decide what to do if the condition
matched more than one row, but I'd be happy with _whatever_ was decided
in that regard.

To be honest, I'd even pay the six-query price that Christopher talks
about for the improved ease of maintainability of my scripts that I
would gain. If query efficiency was a concern then I would code around
that as required, because that is usually the exception especially for
the sorts of situations where INSERT OR UPDATE syntax is useful.

> 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.

You're probably right. I do remember they use CREATE OR REPLACE for
procedures and such, but I don't use Oracle too much nowadays :-)

Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?