MySQL Forums
Forum List  »  InnoDB

Re: Update a row or insert if don´t exist
Posted by: Arjen Lentz
Date: June 13, 2005 11:06PM

Francisco Morales wrote:
> Hello, I am trying to insert a rown if dont exist on the table or update it if
> exits, i am trying with this code, but it doent works.

Not surprising really - you can't just make up your own syntax ;-)

> UPDATE productosdet set pro_id = '1' WHERE pro_id = '1'
> ELSE INSERT INTO productosdet (productosdet_id, posicion_id, pro_id,
> seccion_id, parent_id) values ('', '', '', 20, 1)
>
> Anybody knows how can I do it ??

Not a problem. The following construct has been implemented in MySQL 4.1:

INSERT (your insert statement)
ON DUPLICATE KEY
UPDATE (your update statement)

This construct tries to INSERT a row; if this fails, due to a PRIMARY or UNIQUE key constraint (duplicate key), the UPDATE is executed. The update is actually completely independent from the insert, so in theory you could even do something in another table.

Anyway, I think this construct does exactly what you need.

Regards, Arjen.
--
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Remote expertise & maintenance for MySQL/MariaDB server environments.

Follow us at http://openquery.com/blog/ & http://twitter.com/openquery

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Update a row or insert if don´t exist
4782
June 13, 2005 11:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.