Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
3  
UPDATE ....... –  FreshPhilOfSO Aug 17 '12 at 20:39
1  
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
add comment

2 Answers

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

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

Your Answer

 
discard

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.