Hello.
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?
