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
- UPDATE query
- mysql_affected_rows()
- 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'.