What's the best way to update n items in relation to one item?

Question

I apologize that the title may not make sense, so let me describe my problem. I have a table for Questions, and a table for Answers. One Question has many Answers. When I create a Question, I simply INSERT as many Answers as were provided. The problem comes into play when I have to update the Answers (either adding new ones, editing existing ones, or removing existing ones). My current strategy is to simply delete all existing Answers for the particular Question, and then run a new batch of INSERTS. This seems highly inefficient to me, and I've now run into the situation where I need to maintain the Answers' primary IDs, and deleting the Answers makes this impossible. How can I update the Answers without first deleting them?

As a side note, I'm using Symfony + Doctrine for PHP. I don't know if Doctrine has something built in that can help me, but thought it was worth mentioning.

Thanks

Answer 1

Why can't you just do something along the lines of:

UPDATE Answers SET ... WHERE (question_id = ...);

Am I (or is the question) missing something here?

Answer 2

Can you handle adding, editing and deleting as three separate cases?

If the user wants to edit or delete an answer, you've probably just displayed it, so you can also pass along its ID when they submit the request to edit or delete it. In which case you can do an UPDATE or DELETE with a clause like "WHERE answer_id = 4"

Answer 3

Look into REPLACE INTO or INSERT ... ON DUPLICATE KEY ..