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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
zeek
Forum Commoner
Posts: 48
Joined: Mon Feb 27, 2006 7:41 pm

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

Post 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.
Last edited by zeek on Fri Dec 29, 2006 6:25 pm, edited 1 time in total.
paladaxar
Forum Commoner
Posts: 85
Joined: Fri Jun 18, 2004 11:50 pm

Post 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;
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

  1. UPDATE query
  2. mysql_affected_rows()
  3. INSERT query
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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;
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Wouldn't mysql's REPLACE keyword be ideal for this? :?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I'd probably opt for INSERT INTO ON DUPLICATE KEY if i were using mysql...
User avatar
zeek
Forum Commoner
Posts: 48
Joined: Mon Feb 27, 2006 7:41 pm

Post 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'.
Post Reply