Updating the MySQL table, deleting old records.

Question

I have a MySQL associative table (user_category) that stores user preferences for categories. It has a UserId column and a CategoryCode column. If a user was interested in travel categories (trvl) and a free category (free), the entry would be like this.

UserId    CategoryCode
1         trvl
1         free

What is the best way to update this entry when a user updates their preferences in a category? I thought the easiest way is just

DELETE FROM user_category WHERE UserId = 1;
INSERT INTO user_category (UserId,CategoryCode) VALUES (1,'catx'),(1,'catx'),(1,'catx')

Where "catx" are the new categories that interest them.

Answer 1

Several times the simplest solutions are the best. And this is one of these times;)

Answer 2

. , , . , .

-- Delete just the preferences that are no longer valid
DELETE  user_category 
from    user_category a left join
        NewSelection b on a.UserId=b.UserId and a.CategoryCode=b.CategoryCode
WHERE   b.CategoryCode is null

-- Insert just the new preferences
INSERT INTO user_category (UserId,CategoryCode) 
SELECT  a.UserId,a.CategoryCode
FROM    NewSelection a left join
        user_category b on a.UserId=b.UserId and a.CategoryCode=b.CategoryCode
where   b.CategoryCode is null

Answer 3

delete from user_category where UserId = 1 and CategoryCode not in (x, y, z)
insert into user_category values (1, x) on duplicate key update CategoryCode = x

Suppose your primary key is UserId, CategoryCode.