Page 1 of 1

[SOLVED] mySQL: how to... update if exists, else insert?

Posted: Fri Dec 29, 2006 2:43 pm
by zeek
The query below is all wrong, I know. It just seemed easier to ask my question this way. What would be the correct way to write...

Code: Select all

IF (SELECT * FROM t1 WHERE page='home.htm') 
    UPDATE t1 SET hits=(hits+1) WHERE page='home.htm'
ELSE
    INSERT INTO t1 (page, hits) VALUES ('home.htm', 1)
END IF
In other words, How would I write a query that updates other columns of a row if foo='bar', or inserts a new row if none are found (where foo='bar')? Thanks in advance.

Posted: Fri Dec 29, 2006 3:31 pm
by paladaxar
I would produce the logic using PHP, and keep the queries simple.

Something like this:

Code: Select all

$query = "select stuff from place";
    $result = mysql_query($query);
    $num_rows = mysql_num_rows($result);
    if ( $num_rows > 0 )
         run update query;
    else
         run insertion query;

Posted: Fri Dec 29, 2006 3:38 pm
by feyd
  1. UPDATE query
  2. mysql_affected_rows()
  3. INSERT query

Posted: Fri Dec 29, 2006 4:23 pm
by RobertGonzalez
Depending upon your MySQL version you could do something like

Code: Select all

IF EXISTS (SELECT * FROM t1 WHERE page='home.htm') THEN
    UPDATE t1 SET hits=hits+1 WHERE page='home.htm';
ELSE
    INSERT INTO t1 (page, hits) VALUES ('home.htm', 1);
END IF;

Posted: Fri Dec 29, 2006 4:53 pm
by Luke
Wouldn't mysql's REPLACE keyword be ideal for this? :?

Posted: Fri Dec 29, 2006 5:25 pm
by RobertGonzalez
I use that same if exists logic in a lot of my stored procedures. It should work without incident provided you have the right version.

Posted: Fri Dec 29, 2006 5:34 pm
by timvw
I'd probably opt for INSERT INTO ON DUPLICATE KEY if i were using mysql...

Posted: Fri Dec 29, 2006 6:25 pm
by zeek
Thank you all for your replies.

I couldn't find the solution googling, but from researching your responses I've found that like Everah suggested, my attempted solution using the IF statement requires mySQL >= 5.0, but I'm developing for mySQL 4.1.

INSERT INTO ON DUPLICATE KEY will work great. Thank you timvw. I guees I need to make the 'foo' column UNIQUE, and that way I can do it with a single query. I should have been able to find this, but for what ever reason I didn't.

http://dev.mysql.com/doc/refman/4.1/en/ ... icate.html >>>
If you specify ON DUPLICATE KEY UPDATE (added in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;
Thanks again for all of your replies. I'm marking the thread 'SOLVED'.