What's an example of a *single* MySQL query that will insert a row only if it doesn't already exist?

Question

Typically I do a:

SELECT * FROM x WHERE id=y

I check to see if any rows are returned. If there are, I then execute an UPDATE, and if not, I do an INSERT.

I'd love to have a basic template that does the check and incorporates the UPDATE and INSERT operations into a single MySQL query rather than doing it in an external language like java or php.

Answer 1

If you are using MySQL 4.1 or later, you can take advantage of the "ON DUPLICATE KEY UPDATE" syntax. For your example you would need to specify that "id" is either a primary key or create a unique index on "id". You can then have a single statement that looks something like:



insert into x (id, col1, col2, ...) values (y, value1, value2, ...)

on duplicate key update

col1 = value1, col2 = value2, ...

Answer 2

whilst the database isn't looking the '$call' cost on your container it rather is returning 0 rows. I wager your Hypertext Preprocessor code assumes which you're starting to be a minimum of one row back. for this reason the errors whilst the source would not have something in it. There are 2 approaches around it. the two you are able to alter your sq. to $question = "pick count extensive type(*) FROM purchasers the place call='$call';"; which will assure you a result the place you are able to in simple terms verify that it rather is better than 0 or you need to use the mysql_num_rows() functionality to envision which you fairly have a row earlier you fetch the 1st row e.g. // does person exist? $call = mysql_real_escape_string($call); $question = "pick * FROM purchasers the place call='$call';"; $res = mysql_query($question); if (mysql_num_rows($res) > 0) { // sure, pull interior the person information } else // no, person would not exist }

Answer 3

no , you are doing it the wrong way, you should use a primary key or unique key to identify the fields that are unique .It will give duplicate entry for key error message, when duplicates are inserted into unique keys,. after execution of the insert statement get the error message, if there is one, then do an update