question to sql-statements - Printable Version +- Kodi Community Forum (https://forum.kodi.tv) +-- Forum: Development (https://forum.kodi.tv/forumdisplay.php?fid=32) +--- Forum: Kodi Application (https://forum.kodi.tv/forumdisplay.php?fid=93) +--- Thread: question to sql-statements (/showthread.php?tid=222428) |
question to sql-statements - SaEt9000 - 2015-03-25 Hi, I hope that I'm right here with my question. I would like to make some general changes in my MyVideosXX.DB. Therefore I'm using a sqlllite-program on windows PC. The access works fine. No I would like to create some sql-statements to set some (pre-)videosettings without starting every video on kodi first. F.i : when I do this: SELECT * FROM episodeview where episodeview.strTitle="The Mentalist" I'm getting a list with all searched episodes. After that I can do a statement like this: insert OR replace into settings (idfile,Deinterlace, ViewMode, ZoomAmount, PixelRatio, VerticalShift, Audiostream, SubtitleStream, Subtitledelay, SubtitlesOn, Brightness, Contrast, Gamma) VALUES ( "VALUEidfile",5,0,1,1,0,1,-1,0,0,50,50,20) This last line I have to do manualy for every value I got in the first question. It works but that was not my idea. That is not so much faster then access every file on kodi and set the values manualy :-) My idea was to work with statements like this: First: INSERT INTO settings (idFile) SELECT idFile FROM episodeview where episodeview.strTitle="The Mentalist" GROUP BY idFile This should insert all needed lines in the database (only filled the field idFile) - but it is NOT working After that I thought to update the database with this statement: UPDATE settings SET Deinterlace=5, ViewMode=0, ZoomAmount=1, PixelRatio=1, VerticalShift=0, Audiostream=-1, SubtitleStream=-1, Subtitledelay=0, SubtitlesOn=0, Brightness=50, Contrast=50, Gamma=20 where Deinterlace=0 That I had tested successfully... So is my question: how can i create statements which are able to select idfile-IDs from Table "episodeview" and fill it direct to the Table "settings" ? Just one idea... should I better work with temporary tables.. ? Thanks for your help or your ideas SaEt9000 RE: question to sql-statements - wsnipex - 2015-03-26 blind guess (my sql is very rusty): INSERT INTO settings AS SELECT idFile FROM episodeview where episodeview.strTitle="The Mentalist" GROUP BY idFile RE: question to sql-statements - SaEt9000 - 2015-03-26 Thanks for your reply - I will try it. SaEt9000 RE: question to sql-statements - SaEt9000 - 2015-03-29 it was not far from your hint - but at the end this here is working: insert OR replace INTO settings (idFile) SELECT idFile FROM episodeview where episodeview.strTitle="The Mentalist" GROUP BY idFile So I'm getting the lines for these episodes in the settings table. Because only one field is filled (and the screen would stay black :-)) I have to update the lines after that with that update command: UPDATE settings SET Deinterlace=5, ViewMode=0, ZoomAmount=1, PixelRatio=1, VerticalShift=0, Audiostream=-1, SubtitleStream=-1, Subtitledelay=0, SubtitlesOn=0, Brightness=50, Contrast=50, Gamma=20 where Deinterlace is null SaEt9000 |