Fastest way to update a MySQL table if row exists else insert. More than 2 non-unique keys
11,557
just use INSERT...ON DUPLICATE KEY UPDATE
INSERT INTO reports_adv (day, uid, siteid, cid, visits)
VALUES ('$day', '$uid', '$sid', '$cid', 1)
ON DUPLICATE KEY UPDATE visits=visits+1;
but before anything else, you should define a UNIQUE
constraint on the columns.
ALTER TABLE reports_adv ADD CONSTRAINT tb_uq UNIQUE (day, uid, siteid, cid)
Author by
dracosu
Updated on June 07, 2022Comments
-
dracosu almost 2 years
I have the following table structure:
CREATE TABLE IF NOT EXISTS `reports` ( `id` int(11) NOT NULL AUTO_INCREMENT, `day` int(11) NOT NULL, `uid` int(11) NOT NULL, `siteid` int(11) NOT NULL, `cid` int(3) NOT NULL, `visits` int(11) NOT NULL, PRIMARY KEY (`id`) )
Currently i check & insert/update with the following snippet:
$checkq = mysql_query("SELECT count(*) as rowexist FROM reports WHERE day='$day' AND uid='$uid' AND siteid='$sid' AND cid='$cid'") or die(mysql_error()); $checkr = mysql_fetch_array($checkq); if ($checkr['rowexist'] > 0) { mysql_query("UPDATE reports_adv SET visits=visits+1 WHERE day='$day' AND uid='$uid' AND siteid='$sid' AND cid='$cid'"); } else { mysql_query("INSERT INTO reports_adv SET day='$day', uid='$uid', siteid='$sid', cid='$cid', visits='1'"); }
Is a fastest way to update this MySQL table if row exists else insert with more than 2 non-unique keys?