How to remove duplicate notes, once and for all

nikosvaggalis · 2024-06-19T07:06:23.402Z

I managed to do this using raw sql. Open the joplin sqlite database with a tool like DB Browser and then run the following sql:

delete from notes where id in (
select id from (
select id,rn from (
select a.id,row_number() over (PARTITION by title order by id) as rn
from notes a
where a.title<>''
)
where rn>1
)
)

this will keep only ONE note from the duplicate ones. Duplicates are considered the ones who have the same title,but you can adjust your criteria.
Of course, use it at your own risk.I accept no responsibility for anything that'll go wrong.
It's best to test first. For that replace 'delete from' with 'select * from '

JackGruber · 2024-06-19T08:55:54.187Z

That is highly risky!

If a sync is set up, this can lead to synchronization problems!

I absolutely advise against doing this unless you know 100% how Joplin works internally.

Always use the API to delete/edit notes, do not edit the Synctarget files or sqlite database directly!

nikosvaggalis · 2024-06-19T09:11:18.515Z

You mean sunc your local notes with the cloud? Well I didn't consider the sync aspect because I'm not using this feature.It is all stored locally on my PC with sync off.

JackGruber · 2024-06-19T10:02:30.975Z

Yes, especially if you want to synchronize with other devices, regardless of the method. But also if you use Joplin without Sync.

For example, what about foreign keys?
Resources, tags refer to notes that no longer exist. What happens if a tag is opened in the GUI that refers to notes that no longer exist because they have been deleted directly from the database? I don't know, Everything can be ok, Joplin can behave strangely or crash etc...

nikosvaggalis · 2024-06-19T10:59:53.548Z

I opened the database first with Jailer so that I can take a peek at the schema but there's no FK constraints etc.You have to do some investigation first.For example the parent_id of table Notes is the id of the Folder, whilst the notes who have tags can be found in note_tags.
Sure it's risky business, but so far my experiments seems to have gone well.I did a VACCUM at the end which saved off 3gb