Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.