SQLite - One liner/command Drop Table based on query of sqlite_master?How to list the tables in a SQLite database file that was opened with ATTACH?SQL update from one Table to another based on a ID matchHow do I check in SQLite whether a table exists?SQLite table creation dateIs there an SQLite equivalent to MySQL's DESCRIBE [table]?What are the options for storing hierarchical data in a relational database?How to drop a table if it exists in SQL Server?SQLite alter table with result from select statementIterate over sqlite tables in an efficient waySQLite query for two tables
If a centaur druid Wild Shapes into a Giant Elk, do their Charge features stack?
Symmetry in quantum mechanics
What do you call words made from common English words?
Shall I use personal or official e-mail account when registering to external websites for work purpose?
How did the USSR manage to innovate in an environment characterized by government censorship and high bureaucracy?
Is domain driven design an anti-SQL pattern?
Are objects structures and/or vice versa?
How to answer pointed "are you quitting" questioning when I don't want them to suspect
Is there a name of the flying bionic bird?
Is this food a bread or a loaf?
"listening to me about as much as you're listening to this pole here"
"My colleague's body is amazing"
What is the meaning of "of trouble" in the following sentence?
Calculate Levenshtein distance between two strings in Python
Add an angle to a sphere
What is the command to reset a PC without deleting any files
Are white and non-white police officers equally likely to kill black suspects?
Is it wise to focus on putting odd beats on left when playing double bass drums?
Why was the "bread communication" in the arena of Catching Fire left out in the movie?
Landlord wants to switch my lease to a "Land contract" to "get back at the city"
Re-submission of rejected manuscript without informing co-authors
What does 'script /dev/null' do?
Ideas for 3rd eye abilities
I see my dog run
SQLite - One liner/command Drop Table based on query of sqlite_master?
How to list the tables in a SQLite database file that was opened with ATTACH?SQL update from one Table to another based on a ID matchHow do I check in SQLite whether a table exists?SQLite table creation dateIs there an SQLite equivalent to MySQL's DESCRIBE [table]?What are the options for storing hierarchical data in a relational database?How to drop a table if it exists in SQL Server?SQLite alter table with result from select statementIterate over sqlite tables in an efficient waySQLite query for two tables
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a database called HelpfulStats.sqlite3 which stores helpful stats about the days operation of the software it belongs to.
Inside HelpfulStats.sqlite3 I have tables that follow a naming convention like so: stats_YYMMDD. During startup of the software, I check to see what the file size of HelpfulStats.sqlite3 is and if it exceeds a set size threshold I call VACUUM on it, check to see if it's still too big, and then I run a query to delete the oldest stats_ table if necessary.
I am successfully finding the oldest table by using SELECT [tbl_name] FROM sqlite_master WHERE type='table' AND [tbl_name] LIKE 'stats_%' ORDER BY [tbl_name] ASC LIMIT 1. However, I can't seem to figure out how to pipe that SELECT result into a DROP TABLE command. Is it possible?
I can easily just make them two separate queries, one to capture the result of the SELECT and then one to execute the DROP with that table name as a parameter but I'm wondering if it's possible to do it purely in one SQL execution.
I've tried things like DROP TABLE (SELECT [tbl_name] FROM sqlite_master WHERE type='table' AND [tbl_name] LIKE 'stats_%' ORDER BY [tbl_name] ASC LIMIT 1) but I get syntax errors on the first opening parenthesis. Is what I'm trying to do possible?
sql sqlite sqlite3
asked Mar 22 at 1:49
HDL_CinC_DragonHDL_CinC_Dragon
1567
add a comment |
I have a database called HelpfulStats.sqlite3 which stores helpful stats about the days operation of the software it belongs to.
Inside HelpfulStats.sqlite3 I have tables that follow a naming convention like so: stats_YYMMDD. During startup of the software, I check to see what the file size of HelpfulStats.sqlite3 is and if it exceeds a set size threshold I call VACUUM on it, check to see if it's still too big, and then I run a query to delete the oldest stats_ table if necessary.
I am successfully finding the oldest table by using SELECT [tbl_name] FROM sqlite_master WHERE type='table' AND [tbl_name] LIKE 'stats_%' ORDER BY [tbl_name] ASC LIMIT 1. However, I can't seem to figure out how to pipe that SELECT result into a DROP TABLE command. Is it possible?
I can easily just make them two separate queries, one to capture the result of the SELECT and then one to execute the DROP with that table name as a parameter but I'm wondering if it's possible to do it purely in one SQL execution.
I've tried things like DROP TABLE (SELECT [tbl_name] FROM sqlite_master WHERE type='table' AND [tbl_name] LIKE 'stats_%' ORDER BY [tbl_name] ASC LIMIT 1) but I get syntax errors on the first opening parenthesis. Is what I'm trying to do possible?
sql sqlite sqlite3
asked Mar 22 at 1:49
HDL_CinC_DragonHDL_CinC_Dragon
1567
1
You're better off using a single table with a column that records the date instead of one table per day. Then it's just a simpleDELETE FROM stats WHERE date < '2019-03-15'or whatever to clean it up.
– Shawn
Mar 22 at 2:39
That's a really good point though in my particular use case it makes more sense to group each day into a table. Or, I suppose I could make a catalog table to achieve what I'm looking for as well. Hmmm
– HDL_CinC_Dragon
Mar 22 at 4:01
add a comment |
I have a database called HelpfulStats.sqlite3 which stores helpful stats about the days operation of the software it belongs to.
Inside HelpfulStats.sqlite3 I have tables that follow a naming convention like so: stats_YYMMDD. During startup of the software, I check to see what the file size of HelpfulStats.sqlite3 is and if it exceeds a set size threshold I call VACUUM on it, check to see if it's still too big, and then I run a query to delete the oldest stats_ table if necessary.
I am successfully finding the oldest table by using SELECT [tbl_name] FROM sqlite_master WHERE type='table' AND [tbl_name] LIKE 'stats_%' ORDER BY [tbl_name] ASC LIMIT 1. However, I can't seem to figure out how to pipe that SELECT result into a DROP TABLE command. Is it possible?
I can easily just make them two separate queries, one to capture the result of the SELECT and then one to execute the DROP with that table name as a parameter but I'm wondering if it's possible to do it purely in one SQL execution.
I've tried things like DROP TABLE (SELECT [tbl_name] FROM sqlite_master WHERE type='table' AND [tbl_name] LIKE 'stats_%' ORDER BY [tbl_name] ASC LIMIT 1) but I get syntax errors on the first opening parenthesis. Is what I'm trying to do possible?
sql sqlite sqlite3
asked Mar 22 at 1:49
HDL_CinC_DragonHDL_CinC_Dragon
1567
I have a database called HelpfulStats.sqlite3 which stores helpful stats about the days operation of the software it belongs to.
Inside HelpfulStats.sqlite3 I have tables that follow a naming convention like so: stats_YYMMDD. During startup of the software, I check to see what the file size of HelpfulStats.sqlite3 is and if it exceeds a set size threshold I call VACUUM on it, check to see if it's still too big, and then I run a query to delete the oldest stats_ table if necessary.
I am successfully finding the oldest table by using SELECT [tbl_name] FROM sqlite_master WHERE type='table' AND [tbl_name] LIKE 'stats_%' ORDER BY [tbl_name] ASC LIMIT 1. However, I can't seem to figure out how to pipe that SELECT result into a DROP TABLE command. Is it possible?
I can easily just make them two separate queries, one to capture the result of the SELECT and then one to execute the DROP with that table name as a parameter but I'm wondering if it's possible to do it purely in one SQL execution.
I've tried things like DROP TABLE (SELECT [tbl_name] FROM sqlite_master WHERE type='table' AND [tbl_name] LIKE 'stats_%' ORDER BY [tbl_name] ASC LIMIT 1) but I get syntax errors on the first opening parenthesis. Is what I'm trying to do possible?
sql sqlite sqlite3
sql sqlite sqlite3
asked Mar 22 at 1:49
HDL_CinC_DragonHDL_CinC_Dragon
1567
asked Mar 22 at 1:49
HDL_CinC_DragonHDL_CinC_Dragon
1567
asked Mar 22 at 1:49
HDL_CinC_DragonHDL_CinC_Dragon
1567
asked Mar 22 at 1:49
HDL_CinC_DragonHDL_CinC_Dragon
1567
asked Mar 22 at 1:49
HDL_CinC_DragonHDL_CinC_Dragon
1567
1567
1
You're better off using a single table with a column that records the date instead of one table per day. Then it's just a simpleDELETE FROM stats WHERE date < '2019-03-15'or whatever to clean it up.
– Shawn
Mar 22 at 2:39
That's a really good point though in my particular use case it makes more sense to group each day into a table. Or, I suppose I could make a catalog table to achieve what I'm looking for as well. Hmmm
– HDL_CinC_Dragon
Mar 22 at 4:01
add a comment |
1
You're better off using a single table with a column that records the date instead of one table per day. Then it's just a simpleDELETE FROM stats WHERE date < '2019-03-15'or whatever to clean it up.
– Shawn
Mar 22 at 2:39
That's a really good point though in my particular use case it makes more sense to group each day into a table. Or, I suppose I could make a catalog table to achieve what I'm looking for as well. Hmmm
– HDL_CinC_Dragon
Mar 22 at 4:01
1
1
You're better off using a single table with a column that records the date instead of one table per day. Then it's just a simple
DELETE FROM stats WHERE date < '2019-03-15' or whatever to clean it up.– Shawn
Mar 22 at 2:39
You're better off using a single table with a column that records the date instead of one table per day. Then it's just a simple
DELETE FROM stats WHERE date < '2019-03-15' or whatever to clean it up.– Shawn
Mar 22 at 2:39
That's a really good point though in my particular use case it makes more sense to group each day into a table. Or, I suppose I could make a catalog table to achieve what I'm looking for as well. Hmmm
– HDL_CinC_Dragon
Mar 22 at 4:01
That's a really good point though in my particular use case it makes more sense to group each day into a table. Or, I suppose I could make a catalog table to achieve what I'm looking for as well. Hmmm
– HDL_CinC_Dragon
Mar 22 at 4:01
add a comment |
1 Answer
1
active
oldest
votes
As far as I know, SQLite does not support dynamic SQL queries, which is what you would need to do what you are attempting. One workaround would be make your select call against sqlite_master from an app language (e.g. Java, Python, Node.js), and then build the delete query using code.
answered Mar 22 at 1:52
Tim BiegeleisenTim Biegeleisen
238k13100160
Thanks for the reply. That's what I figured I'd have to do but just wanted to be sure there wasn't a cleaner way. Thanks again.
– HDL_CinC_Dragon
Mar 22 at 2:19
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55291751%2fsqlite-one-liner-command-drop-table-based-on-query-of-sqlite-master%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
As far as I know, SQLite does not support dynamic SQL queries, which is what you would need to do what you are attempting. One workaround would be make your select call against sqlite_master from an app language (e.g. Java, Python, Node.js), and then build the delete query using code.
answered Mar 22 at 1:52
Tim BiegeleisenTim Biegeleisen
238k13100160
Thanks for the reply. That's what I figured I'd have to do but just wanted to be sure there wasn't a cleaner way. Thanks again.
– HDL_CinC_Dragon
Mar 22 at 2:19
add a comment |
As far as I know, SQLite does not support dynamic SQL queries, which is what you would need to do what you are attempting. One workaround would be make your select call against sqlite_master from an app language (e.g. Java, Python, Node.js), and then build the delete query using code.
answered Mar 22 at 1:52
Tim BiegeleisenTim Biegeleisen
238k13100160
Thanks for the reply. That's what I figured I'd have to do but just wanted to be sure there wasn't a cleaner way. Thanks again.
– HDL_CinC_Dragon
Mar 22 at 2:19
add a comment |
As far as I know, SQLite does not support dynamic SQL queries, which is what you would need to do what you are attempting. One workaround would be make your select call against sqlite_master from an app language (e.g. Java, Python, Node.js), and then build the delete query using code.
answered Mar 22 at 1:52
Tim BiegeleisenTim Biegeleisen
238k13100160
As far as I know, SQLite does not support dynamic SQL queries, which is what you would need to do what you are attempting. One workaround would be make your select call against sqlite_master from an app language (e.g. Java, Python, Node.js), and then build the delete query using code.
answered Mar 22 at 1:52
Tim BiegeleisenTim Biegeleisen
238k13100160
answered Mar 22 at 1:52
Tim BiegeleisenTim Biegeleisen
238k13100160
answered Mar 22 at 1:52
Tim BiegeleisenTim Biegeleisen
238k13100160
answered Mar 22 at 1:52
Tim BiegeleisenTim Biegeleisen
238k13100160
238k13100160
Thanks for the reply. That's what I figured I'd have to do but just wanted to be sure there wasn't a cleaner way. Thanks again.
– HDL_CinC_Dragon
Mar 22 at 2:19
add a comment |
Thanks for the reply. That's what I figured I'd have to do but just wanted to be sure there wasn't a cleaner way. Thanks again.
– HDL_CinC_Dragon
Mar 22 at 2:19
Thanks for the reply. That's what I figured I'd have to do but just wanted to be sure there wasn't a cleaner way. Thanks again.
– HDL_CinC_Dragon
Mar 22 at 2:19
Thanks for the reply. That's what I figured I'd have to do but just wanted to be sure there wasn't a cleaner way. Thanks again.
– HDL_CinC_Dragon
Mar 22 at 2:19
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55291751%2fsqlite-one-liner-command-drop-table-based-on-query-of-sqlite-master%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
You're better off using a single table with a column that records the date instead of one table per day. Then it's just a simple
DELETE FROM stats WHERE date < '2019-03-15'or whatever to clean it up.– Shawn
Mar 22 at 2:39
That's a really good point though in my particular use case it makes more sense to group each day into a table. Or, I suppose I could make a catalog table to achieve what I'm looking for as well. Hmmm
– HDL_CinC_Dragon
Mar 22 at 4:01