Question: I've 3 tables.

Users (id, mail, username, etc..)

Practices (id, name)

UsersPractices (userId, practiceId)

The last one is the relational table which is n-n.

I'd like to update this one, depending on the user's will.

It means, that he could want to add, or remove some his practices.

What algorithm could i use to do this ?

Should it be better to let this job (if there is a way) to the database engine ?
Or should i write my own algorithm to handle the data, then do my requests to the db ?

**EDIT:**

To be clear :

```
___________________________
| UserId    |  PracticeId |
|-----------|-------------|
|    12     |     21      |
|-----------|-------------|
|    12     |     18      |
|-----------|-------------|

```

Maybe, the user will try to change his practice from 21 to 15 but wants to keep the practice 18.

So, from the request, i'll get practices = array(15,18);

that means the users practice will look like : 

```
___________________________
| UserId    |  PracticeId |
|-----------|-------------|
|    12     |     15      |
|-----------|-------------|
|    12     |     18      |
|-----------|-------------|

```

SO what is the best way to achieve this ?

Should i select & check each practice, and then, delete if needed ?

Delete them all, and add the news.

Answer: 
Assuming you have InnoDB tables, do it as a transaction.

The basic idea here is to wipe the slate clean and then create/re-create the associations. This is easier than determining the delta of each update (i.e., inspecting each row individually and determining if it's an INSERT, UPDATE, or DELETE action).

Here's the raw SQL you would use

```
BEGIN;

DELETE FROM UsersPractices
 WHERE userId = [User ID];

INSERT INTO UsersPractices (
       userId
     , practiceId)
VALUES ([User ID], [Practice ID 1])
     , ([User ID], [Practice ID 2])
     ...
     , ([User ID], [Practice ID N]);

COMMIT;

```
If you're executing SQL statements directly using the `Zend_Db` adapter, you could build an `UPDATE` query based on the choices the user wants to make. For example:

```
$db->query('UPDATE UsersPractices
    SET practiceId = 
      CASE practiceId
       WHEN 21 THEN 15
       ELSE practiceId 
      END
    WHERE userId = ?', 
  array($userId));

```

This would change `practiceId` to 15 only on the row where `practiceId` is 21. On other rows, it would make no change to the `practiceId` column.