I’ve been trying to change the creation date of some cards, specifically those added before September 16, 2022, but I’ve been running into a few problems. They mainly seem to be caused, from what I understand, by the fact that a card’s ID is determined by its creation date, and that same ID is what links the card to the review log in Anki’s database structure.
This means that every time I change the date a card was added (via the Debug Console), the card loses its connection to the review log, effectively “deleting” all the reviews I’ve done for it—or at least disconnecting them from the card itself.
I’ve tried, through various approaches, to ensure that changes to the card IDs in the cards table are mirrored in the review log table so that each card retains its review history. However, no matter what I do, even after making sure both tables match, the card still appears as if it has no review history.
At this point, I feel like part of the problem is my lack of SQL knowledge. Has anyone more experienced than me successfully changed card IDs while keeping reviews intact?
Why do you want to change the card creation date? Just want to make sure there is no xy-problem here.
Which exact commands did you use to update your database? Please put them into codeblocks like this (otherwise this site might eat important formatting):
```
Code goes here.
```
To answer your first question, this is because I have downloaded many shared anki decks, and for some reason, many of the cards i downloaded kept their old creation date. This means that whenever I look at my stats, according to anki, I have created some cards, say, 4000 days before when I actually started using the application (Note that I have already studied and reviewed most of these downloaded cards for quite some time by this point, it would be a shame to lose all the progress).
Here are the prompts i used:
"mw.col.db.execute("""
-- Step 1: Update card IDs for all cards created before September 16, 2022
UPDATE cards
SET id = (
SELECT MIN(id)
FROM revlog
WHERE revlog.cid = cards.id
)
WHERE id < 1663243200000
AND EXISTS (SELECT 1 FROM revlog WHERE revlog.cid = cards.id)
""")
mw.col.db.execute("""
-- Step 2: Ensure reviewlog entries reflect the updated card IDs
UPDATE revlog
SET cid = (
SELECT id
FROM cards
WHERE cards.id = revlog.cid
)
WHERE revlog.cid < 1663243200000
AND EXISTS (SELECT 1 FROM cards WHERE cards.id = revlog.cid)
""")
# Update schema to apply changes
mw.col.mod_schema(False)
I wont lie, I used ChatGPT to make these, I have little knowledge of this stuff. I don’t know if it did any errors while writing these, and that might be the issue.
Nah, it’s just an information thing. It just bothers me that I can’t see the timeline of when I created certain cards. Unless I’m missing an easy fix and it’s actually a big effort to change, I’ll just stick with this.
I wouldn’t have necessarily guessed that changing the creation date would also impact the card ID, but the logic is sound, so I find myself unsurprise. Personally, I’m quite squeamish about database editing – unless you really, really, really know what you’re doing, there are a lot of things you can break accidentally. This seems like a high-risk, low-reward venture.
I think you’ll be better off just leaving this one be. If it makes you feel any better, this is what Added looks like for everyone who uses shared decks. Did I add a bunch of notes/cards 10 years ago, and then a ridiculous number all at once 3 years ago? Nope.
If you want ideas about how to make your Stats give you an accurate version of that graph, there are also low-to-no-risk ways to get there – like keeping added and imported cards in separate subdecks, tagging imported notes/cards to filter them out in the stats window, etc.
