I have a table with fields foreign_key_id | value1 | value2, and I want to update value2 if I have a match for foreign_key_id and value1.
If foreign_key_id or value1 do not exist, I want to insert a new row. Is there a better way to do this than having PHP use a select statement and then branching to either an update or insert?
Edit: value2 can be the same value as in the database, so I cannot run and update, see if affected_rows is 0, and run and insert if it is.
Try using an IF EXISTS to determine whether to execute an UPDATE or an INSERT statement. You can do this in one PHP statement/query.
IF EXISTS(SELECT 1 FROM Mytable WHERE foreign_key_id = f1 AND value1 = v1)
BEGIN
UPDATE Mytable SET value2 = v2
WHERE foreign_key_id = f1 AND value1 = v1;
END
ELSE
BEGIN
INSERT INTO Mytable(foreign_key_id,value1,value2)
VALUES (f1,v1,v2);
END IF;
Yes, you can just run the update, then get the number of affected rows. If this is 0, run the insert. That will save you a select, because it is included in the update.
[edit]
Query as posted in the comments. Insert using select. This will save an select upfront. You can use mysql_affected_rows to get the number of rows inserted. If it returns 0, you can update. It actually contains the select, so I'm not sure if it is fasters (mysql and subselects aren't exactly friends). It will save you a roundtrip to the database however, and that might just make up for that.
insert into yourtable(f1, f2, fr)
select 'value1', 'value2', 'value3'
from dual /* yeah, I'm an Oracle dude */
where
not exists (
select 'x'
from yourtable
where f1 = 'value1' and f2 = 'value2')
insert into yourtable(f1, f2, fr) select 'value1', 'value2', 'value3' where not exists (select 'x' from yourtable where f1 = 'value1' and f2 = 'value2' That will save you a select. You can just update the database, and have this condition in the insert statement. But you'll have to try to see if it is faster.Try this slight modification if you cannot get p.campbell's solution to work
IF EXISTS(SELECT 1 FROM Mytable WHERE foreign_key_id = f1 AND value1 = v1) THEN
UPDATE Mytable SET value2 = v2
WHERE foreign_key_id = f1 AND value1 = v1;
ELSE
INSERT INTO Mytable(foreign_key_id,value1,value2)
VALUES (f1,v1,v2);
END IF;
Best option: Use REPLACE instead of INSERT or UPDATE. It depends on the use of a primary key or unique key.
foreign_key_idunique or primary? What version of MySQL?