Multiple delete insert query alernative

[o0O0o.o0O0o]

I have set of records where when users selects particular records , then i insert all the record ids to new table(A) with with category id


Table A

id --- record_id---category_id --

With every record_id there are many small tasks associated (1 to many).
SO based upon inserted record_ids i also insert task_id in Table B

Table B

id-----record_id-------Task_id -----category_id

This is when user creating new category

THe problem is when user want to edit the category and selects different record_ids , then i have two solutions

1)Either delete all records with that category_id and insert new record ids

But then i also have to delete Table B records also

2) Second is check if record is already there if not inseret it otherwise update it.
But how will i delete those records which were previously present but not now


e,g Initially i had Record_ids = 3,4,5,6,7
New Record_ids = 1,2,3,4

Now i want to add 1,2 and delete 5,6,7 from table
Also i don't know how to deal with Table B task which are realted to those records

Because i have to delete tasks related to record_d , 5,6,7 and then insert tasks for record_id 1,2



Also if user has not chnaged anhthing then i don't want that first i should delete all the records and then again insert it

[Old Pedant]

I wrote this over 8 years ago:
http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=141

I think it's what you are after. No?

If you didn't have the problem of tableB, though, I'd probably tell you to delete and re-add. For a simple small table such as your tableA, the delete-and-add strategy is usually more efficient.

[o0O0o.o0O0o]

I read that article , that is similar but not what i want.

I can't put second hidden field for every record .
If there were all records in the table then can use that technique to see the status of that record and then change it

but there will be records shown in the form which are not in user table and i can't chnage the status.


I was thinking of storing the ids in temporary table and then do the NOT IN operator to delete the other ids and ON Cascade Delete will delete all the tasks associated with it

and any new insertion will turn on the TRIGGER to load the tasks for those newly added ids