UPDATE related_products SET related_counter = related_counter
WHERE .....
only updates if the record exists
INSERT (x,y,z) SELECT ?,?,1 WHERE NOT EXISTS (SELECT 1 FROM
related_products WHERE .....)
Inserts if the key does not exist.
On Sat, 11 Sep 2004 00:02:26 +0200, Gaetano Mendola <mendola@bigfoot.com> wrote:
> Nick wrote:
>
> > I have a table with columns
> > (product_id,related_product_id,related_counter)
> >
> > If product A is related to product B then a record should be created,
> > if the record already exists then the related_counter should be
> > incremented.
> >
> > This is very easy to do with MySQL using INSERT... ON DUPLICATE KEY.
> > Standard or not, it is very usefull.
> >
> > Is there a way to catch the insert error. For example...
> >
> > INSERT INTO related_products (product_id,related_product_id) VALUES
> > (?,?);
> > IF (???error: duplicate key???) THEN
> > UPDATE related_products SET related_counter = related_counter + 1;
> > END IF;
> >
> > -Nick
>
> With a rule you can do it easily ( never tried ).
>
> Regards
> Gaetano Mendola
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>