Re: Check for data before inserting

[Gabriel Ricard]

FYI, as I stated in a previous email, INSERT ..... ON DUPLICATE KEY  
UPDATE .... is only available in MySQL 4.1+, which is still in  
development and not recommended for production use.

- Gabriel

On Friday, October 17, 2003, at 04:04  AM, Director General: NEFACOMP  
wrote:

The REPLACE statement is very dangerous since it first DELETEs the row  
if
found.

Instead you should use :
    INSERT ..... ON DUPLICATE KEY UPDATE ....
Check the manual for clearer syntax
But this one will work if the INSERT would create a Duplicate Key  
entry (I
mean, would violate the index or primary key)

Thanks
Emery
----- Original Message -----
From: "Mike Knox" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, October 17, 2003 09:44
Subject: RE: Check for data before inserting

Traditionally, I'd take the following approach

1) Update - assume it's there
2) If update fails (0 rows) do an insert
Thats assuming that the update case is more prevalent.  If the row is
unlikely to be there - insert and do the update if you get a duplicate
key.


Rgds

Mike



-----Original Message-----
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: 16 October 2003 19:29
To: Mike Tuller
Cc: MySql List
Subject: Re: Check for data before inserting
It sound like you want to use REPLACE instead of the SELECT and
INSERT/UPDATE combo. Replace will insert if the record doesn't exist
and update if it does.
On Thursday, October 16, 2003, at 01:27 PM, Mike Tuller wrote:

I have a shell script that I have data entered into a database, and
instead
of just entering in the data blindly, I want it to check to see if  
the
item
it is entering exists already, and if it does, update the information
rather
than inserting it. So I want to run a select statement, and if  
results
come
back, have the data updated, and if not have it inserted.

I know how to do this in PHP with $query_total_rows. Is there some  
way
in
SQL to do this, or do I need to figure out a way to do it in the  
shell
script?

Thanks,
Mike
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
---------------------------------------------------------------------- 
----
----
Live Life in Broadband
www.telewest.co.uk
The information transmitted is intended only for the person or entity  
to
which
it is addressed and may contain confidential and/or privileged  
material.
Statements and opinions expressed in this e-mail may not represent  
those
of
the company. Any review, retransmission, dissemination or other use  
of, or
taking of any action in reliance upon, this information by persons or
entities
other than the intended recipient is prohibited. If you received this  
in
error, please contact the sender immediately and delete the material  
from
any
computer.


======================================================================= 
=====
==


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:     
http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]