Re: INSERT WHERE NOT EXISTS

On Wednesday 25 June 2003 20:06, Reuben D. Budiardja wrote:
> Hi,
> I am developing application with PHP as the front end, PGSQL as the
> backend. I am trying to figure out what's the best way to do this.
> I want to check if an entry already exists in the table. If it does, then I
> will do
> UPDATE tablename ....
>
> otherwise, I will do
> INSER INTO tablename...
(...)

> I vaguely remember in Oracle, there is something like this:
>
> INSERT INTO mytable
> SELECT 'value1', 'value2'
>     FROM dummy_table
>   WHERE NOT EXISTS
>         (SELECT NULL FROM mytable
>                 WHERE mycondition)
>
> This query will do INSERT, if there is not an entry already in the TABLE
> mytable that match the condition mycondition. Otherwise, the INSERT just
> fails and return 0 (without returning error), so I can check on that and do
> update instead.

This kind of query should work; just leave out the "FROM dummy_table" bit.
(in Oracle it would be "FROM dual").

Ian Barwick
barwick@gmx.net