sql statement to set watched flag using directory name (1 Viewer)

[dihoff]

hi all,

installed a mediaportal update recently, and lost all of my config, including the watched flag for my movies! i started setting the watched flag again manually, but it's way too time consuming.

can someone help me with the sql for setting the watched flag? i have all my directories named with ratings, e.g. "Toy Story 3 c2010_++++" - if there's a + it would indicate that the movie has been watched.

i've installed sql lite, and opened the database, as per this post.

now, i just need the sql, so, something like:

update user_movie_settings
set watched = 1
where user_movie_settings.id = local_media.id
and local_media.fullpath like '%+%'

this obviously being incorrect.

tia,

dh.

 

[fforde]

For what its worth every time you run the Moving Pictures installer it backs up your database. You might be able to restore your old data.

 

[dihoff]

thanks fforde, but there's a lot of big bold writing nowadays warning that restoring old config might cause problems, so i was wary.

anyway, here's the sql in case it's of use to anyone else:

update user_movie_settings
set watched = 1
where id in (
select movie_info_id
from local_media__movie_info
where local_media_id in (
select id
from local_media
where fullpath like '%+%'))

cheers,

dh.