Using REPLACE INTO on MySQL database when 2 colums must match for unique row
I want to have a table which contains 3 fields and update this table using REPLA开发者_高级运维CE to overwrite rows rather than creating new ones when 2 of the fields match subsequent additions.
In other words I want to do something like this to 'table' which has rows 'a', 'b' and 'c'-
REPLACE INTO table SET c = $var_c WHERE a = $var_b AND b=$var_b
So that it will check to see if there is a row where a AND b match and only write c to a new row if there is not already a row to update. (sorry for stating the obvious)
Here is what I have done instead of using REPLACE. This code achieves what I need but I am sure there is a more efficient way to do this. Thanks
$query1 = "SELECT COUNT(*) FROM table WHERE a = $var_a AND b = $var_b";
$result = @mysql_query ($query1);
$num = mysql_fetch_row($result);
if($num[0] != 0) {
$query = "UPDATE table SET c = $var_c WHERE a = $var_a AND b = $var_b";
}
else {
$query = "INSERT INTO table VALUES ($var_a, $var_b, $var_c)";
}
$result = @mysql_query ($query);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
I would recomment not to use REPLACE at all; in general I prefer to INSERT INTO ... ON DUPLICATE KEY UPDATE. REPLACE means DELETE, than INSERT. This is especially dangerous with foreign key constraints with ON DELETE CASCADE clause. Assume you have to tables
CREATE TABLE parent(
id TINYINT UNSIGNED NOT NULL,
data VARCHAR(255) NOT NULL,
PRIMARY KEY( id )
) Engine = InnoDB;
CREATE TABLE child(
id TINYINT UNSIGNED NOT NULL,
parent_id TINYINT UNSIGNED NOT NULL,
PRIMARY KEY( id ),
FOREIGN KEY( parent_id ) REFERENCES parent( id ) ON DELETE CASCADE
) Engine = InnoDB;
Running a REPLACE on the parent table would silently remove all referencing records from table child. Yes, this is somewhat wanted, because you said "ON DELETE CASCADE". Still you have to keep in mind that REPLACE might be dangerous. On the other hand a "ON DELETE NO ACTION" would mean that your REPLACE would fail, for what it seems no sensible reason. In the end there is - IMO - no point in deleting and inserting the same record, usually INSERT INTO ... ON DUPLICATE KEY UPDATE is what you really want and need.
IF EXISTS(SELECT 1 FROM Mytable WHERE a = v1 AND b = v2)
BEGIN
UPDATE Mytable SET c = v3
WHERE a = v1 AND b = v2;
END
ELSE
BEGIN
INSERT INTO Mytable(a,b,c)
VALUES (v1,v2,v3)
END IF
If you have 2 keys on your table on a
and b
you could use this :
$query = "INSERT INTO table VALUES ($var_a, $var_b, $var_c)
ON DUPLICATE KEY UPDATE c = $var_c ";
精彩评论