Help with SQL syntax [SOLVED]

graysky · 2021-01-31 13:47:51

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)

CarbonChauvinist · 2021-01-31 13:56:47

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)

graysky · 2021-01-31 14:28:52

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)

Arch Linux

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

Help with SQL syntax [SOLVED]

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

Re: Help with SQL syntax [SOLVED]

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

Re: Help with SQL syntax [SOLVED]

Board footer