Re: What is the postgres version of mysql's "ON DUPLICATE KEY"

I may have short handed this to much.  I will assume the product A has
an id of 1 and the related product B has an id of 2.  You have a
default on related_counter of 1 I am assuming

INSERT INTO related_products (product_id,related_product_id)
SELECT 1, 2 WHERE NOT EXISTS (SELECT 1
                                                       FROM related_products
                                                       WHERE
product_id = 1 AND related_product_id = 2)


The insert is plain enough but instead of using values you are getting
the data from the select statement.  The select statement returns 1
row of constant values just like the doing the values however no row
is returned if the where clause is not met.  If no row is returned
then nothing can be inserted therefore no error is returned.

So let's look at the where clause it is a if the subselect returns any
value then exists will be true but we invert that with the NOT.  The
subselect returns 1 if a row already exists with product_id and
related_product_id other wise a null row is returned.

You can think of this as a INSERT if the key doesn't already exist.
If you still need more help just let me know :-)

On Sat, 11 Sep 2004 01:17:29 +0100, Ian Linwood
<ian@dinwoodie.freeuk.com> wrote:
> Hello Kevin,
>
> Friday, September 10, 2004, 11:19:58 PM, you wrote:
>
> KB> INSERT (x,y,z) SELECT ?,?,1 WHERE NOT EXISTS (SELECT 1 FROM
> KB> related_products WHERE .....)
>
> could someone walk me through this one? I do not understand it at all.
> apologies for my cluelessness ;-)
>
> --
> Best regards,
>  Ian
>
>