Take the 2-minute tour ×

Whenever a user updates his profile, we conduct a set of queries like so:

DELETE FROM `user_likes` WHERE `ID` = $id
INSERT INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name)

Is there a way to do this all in one step? There has to be a way, because the interest IDs are all different for each user. Keep in mind that the data never actually has to be deleted - it's a set that is always growing.

share|improve this question
UPDATE ....... –  Phil Aug 17 '12 at 20:39
If you don't want to delete anything, why do you run a DELETE statement? –  ypercube Aug 17 '12 at 21:50
I'm assuming OP's likes are an accumulating table (lack of deletion) –  Daniel Li Aug 29 '12 at 15:42

2 Answers 2

up vote 4 down vote accepted

Create a unique pair index for your user_id and interest_id in the user_likes table.

Then use:

INSERT IGNORE VALUES (DEFAULT, $id, $interest_id, $interest_name), ...

to insert the values, having the database check for previous existence of the pair.

share|improve this answer
This is not the same as deleting and inserting a new row. –  ypercube Aug 17 '12 at 20:55
@ypercube, please read my edit to the question. This answer does do what I want (no need for deletion), I wouldn't have accepted it otherwise. –  Joleen Schunk Aug 17 '12 at 21:34

If you just updating the interest id and name, use UPDATE

UPDATE user_likes SET
`interest_id` = $interest_id,
`interest_name` = '$interest_name'
WHERE `ID` = $id;

If you want to mechanically do a DELETE followed by an UPDATE, use REPLACE

REPLACE INTO `user_likes` VALUES (DEFAULT, $id, $interest_id, $interest_name);

Please test these out on a Dev DB Server to make sure this is what you want

share|improve this answer
+1, I hadn't known REPLACE was valid with a DEFAULT value –  Daniel Li Aug 29 '12 at 15:40

Your Answer


