You are not logged in.

#1 2021-01-31 13:47:51

graysky
Wiki Maintainer
From: :wq
Registered: 2008-12-01
Posts: 10,228
Website

Help with SQL syntax [SOLVED]

My goal is to clean up a MariaDB database, dropping entries if files on the file system to which they refer are not present.  In searching, I found this (circa 2015) indicating some SQL to do it but perhaps it is incompatible with the modern MariaDB.  Mine ends in an error.  Any advice is appreciated.

MariaDB [(none)]> USE MyVideos119;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [MyVideos119]> SELECT * FROM WHERE idPath IN ( SELECT idPath FROM path WHERE strPath NOT LIKE 'nfs:/%' OR strPath IS NULL );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE idPath IN ( SELECT idPath FROM path WHERE strPath NOT LIKE 'nfs:/%’...' at line 1
MariaDB [MyVideos119]> 

Last edited by graysky (2021-01-31 15:17:02)


CPU-optimized Linux-ck packages @ Repo-ck  • AUR packagesZsh and other configs

Offline

#2 2021-01-31 13:56:47

CarbonChauvinist
Member
Registered: 2012-06-16
Posts: 321

Re: Help with SQL syntax [SOLVED]

MariaDB [MyVideos119]> SELECT * FROM WHERE idPath...

Looks like you're missing the table name after `FROM` and before `WHERE`?

Last edited by CarbonChauvinist (2021-01-31 13:58:23)


"the wind-blown way, wanna win? don't play"

Offline

#3 2021-01-31 14:28:52

graysky
Wiki Maintainer
From: :wq
Registered: 2008-12-01
Posts: 10,228
Website

Re: Help with SQL syntax [SOLVED]

I think I am barking up the wrong tree with it actually... I found this which looks right.  I have adapted it to the more recent version of the database but need to wait for some downtime before I try running it with the DELETE command.

This works to list a number of rows:

USE MyVideos119;
SELECT * FROM files WHERE idfile not in (select idfile from episode) and idfile not in (select idfile from movie) and idfile not in (select idfile from musicvideo) ORDER BY `idFile` DESC;

From that post I referenced, replacing the SELECT with a DELETE accomplished the desired effect.

Last edited by graysky (2021-01-31 15:16:54)


CPU-optimized Linux-ck packages @ Repo-ck  • AUR packagesZsh and other configs

Offline

Board footer

Powered by FluxBB