Bulk update a record with Ecto | Elixir /Phoenix
Do you update a record by overriding values? Yes, you can. However, sometime we just can’t do it, because sometimes we have to delete some data at the same time. Can you imagine it? Please imagine that the Blog system like WordPress. We can add some categories or tags to a post we have written down, can’t you? When we write an article as your blog with two tags which are like “Mazda” and “vehicle”. Oh, BTW, the database schema like the following.
Blog Database schemaposts table
==========================================
| id | title | body |....post_tags table
==========================================
| id | post_id | tag_id | ...tags table ( master )
==========================================
| id | tag_name |- post has many posts_tags
So, we added “Mazda” and “vehicle” as the tag to our post.
The records are like the following.
The post has "Mazda" and "vehicle" as the tag.posts table
==========================================
| 1 | MyFirstPost | ..... |post_tags
==========================================
| 1 | 1 | 1 |
| 1 | 1 | 2 |tags table ( master )
==========================================
| 1 | Mazda |
| 2 | vehicle |
| 3 | RED |
| 4 | Blue |
| 5 | Green |
...
O.K, a couple of days passed, we want to update tags of the post.
And think that we want to make our tags to be “Blue” and “Mazda”.
So, we need to delete the “vehicle” tag from the post_tag table.
How will you update the records in the post_tags table? As you’ve already known that we just cannot update an old value “Mazda” and insert new tag “Blue”. If we do this, the “vehicle” tag still be in the table. Which means that we have to think to delete it in this case.
Yes, we do have the case we do not need to think about deleting for updating records. It depends on the system design and UI.
However, the system design often changes anytime, unfortunately. And besides, I personally do not like to think anything too much. Therefore, I would like to introduce my way of updating.
Actually, I learned this way from my colleague, though.
My way has 3 parts which are “Delete data”, “Insert new data” and “Update exists data”. Let me write the steps a few details for the above case.
1) Look for the data ( records ) to delete.
2) Look for the data ( records ) to insert.
3) Look for the data ( records ) to update.
4) Delete data if exists.
5) Insert data if exists.
6) Update data if exists.
The final code.
