Modifying Anki database from SQL to correct initial design flaw


I have been studying a deck of thousands of cards created by me for four years. When I designed it, I didn’t have enough knowledge to know how to do it correctly, so I made a mistake in something fundamental in its design. I created two types of notes called WORD and DEFINITION with the same fields and the same information. Each of the notes contains a single card inside. I did this so that I could study in the “front → back” and “back → front” directions. What I should have done is to create a note type called VOCABULARY with two cards inside called WORD_CARD and DEFINITION_CARD so that, by entering the data only once in each note, I could study in the “front → back” and “back → front” direction without having to duplicate the information and thus my database would occupy practically half the size.

Now, the big problem is that I want to make modifications in the database to change the current structure, which as I have explained is badly designed, to the last one that I have explained, which would be the correct one. My goal is the following: to get to have only one type of note called VOCABULARY with two cards inside it called WORDS_CARD and DEFINITION_CARD without losing any study data from the cards.

This is the solution I have thought of.

From Anki, I add a second card inside the DEFINITION type note (so that I already have two cards inside the DEFINITION note, which, to make it easier for you to understand, I will call here WORD_CARD and DEFINITION_CARD respectively, without renaming them in Anki until I finish the whole process). With this change, I export the deck and, using “DB Browser for SQLite”, I open the database and execute the following SQL statements:

DELETE FROM cards WHERE ord=1;
I delete all the cards in the table “cards” whose value “ord” is equal to 1, since they are the cards that Anki has created automatically when creating the CARD_DEFINITION of the VOCABULARY note (which is the old DEFINITION note).

UPDATE cards SET ord=1 WHERE nid=(SELECT id FROM notes WHERE nid=id AND mid=1519833316765);
For cards in the “cards” table whose nid field points to a DEFINITION card type, I assign the value 1 to its “ord” field. In other words, we have moved the data of the first card within the DEFINITION note (the one containing all the study information) to occupy the position of the second card within the DEFINITION note. In the next step, we must make the card inside the WORD note become the first card of the DEFINITION note. Data: the field “mid” with value “1519833316765” indicates note type DEFINITION.

UPDATE cards SET nid=(SELECT id FROM notes WHERE mid=1519833316765 AND sfld IN (SELECT sfld FROM notes WHERE id=nid));
Unlike the previous step, where I already had a note type DEFINITION and what I wanted is for it to point to its second card, here I have a note type WORD that already points to the first card and what I want is for it to point to the note type DEFINITION, so what I do is to look for those records that have the same “sfld” (which is the sort field, in my database, the words that I study, which are unique for each note type) and assign them the “id” of the note type DEFINITION.

DELETE FROM notes WHERE mid=1519833278399;
As the note type WORD is no longer used by any card, I delete it. Fact: the field “mid” with value “1519833278399” indicates note type WORD.

Once this was done, I started to study at the same time a deck with the old design and, in another Anki account, the same deck after modifying it with the steps I have explained here. And something must be wrong, because, when I was about a week after starting to study simultaneously both decks (which I have been studying for about three years, so I only get review words), one day in the old deck I got three words to study and in the modified one only two, although that card came out the next day in the new one. I thought it could be due to the option of burying review cards that were the same, but they were not really the same. I would like to know if anyone sees that there is an error in my approach or everything should be working correctly.

Is there something I’ve made a mistake in not taking into account the changes I’ve made to the database or should everything work correctly?

Could just be fuzz, if the previous review occured after the split.

When I make the database modification, all the review cards are studied for that day. After the modification, I continue to have zero review cards.

Analyzing the data of the card that does not match in the study day in the old database and in the modified one, I have observed how in the modified one (on the right side) a review is repeated on the same day 17-04-2021 (sorry for the language).

Could still be fuzz. On which date did you modify the db?
Also, you have a review on April 17 in both versions, even though the card had only been reviewed on the previous day. Did you use custom study?

Yesterday I made a copy of my database after finishing the study and performed the process explained in my initial message. On the left I show the card that I would have to study today in both databases, the original or old one and the modified one. As you can see, there are duplicate review lines before I have even studied. It must be a problem or feature of Anki, as I don’t do anything to modify the revlog table.

Edit: I noticed that the error happened when I imported the database into the old local account. However, if I create a new local account in Anki desktop and import the modified database, these duplications disappear. I don’t understand why this happens, but I have checked it. Maybe it is due to another factor. I’m going to start studying the modified database at the same time as the old one for a few days and see if they behave the same way.

No, I have not used filtered decks to study.

I was referring to this one:

Check out the link.

The problem of duplicate review was solved as I explained in a later message. I created a new local user in Anki desktop and imported the modified database after applying the sql queries. Having done that, I no longer get differences in the card stats from the old database compared to the new one, but what you can see in the screenshots happens with exactly the same previous data.

When I perform or execute the modification of the database is when I have already reviewed all the cards of that day. The only explanation for this variation in the remaining interval is what is explained in the link you sent me. If a random number of days is added to the study of a card, I cannot be absolutely certain that my modification in the database is correct, that I have not made a mistake with the SQL queries.