Questions tagged [sqlite]
A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
442
questions
0
votes
1answer
28 views
Syntax error in simple trigger
I have a SQLite table with these fields among others
Fld_QuoteNumberRoot string
Fld_Revision integer
Fld_QuoteNumber string
I need to fill ...
1
vote
2answers
66 views
how can I ensure database connectivity on a spotty network? (and allow for entirely offline connectivity)
My place of work has a pretty spotty network... we have quite regular (though usually brief) outtages.
Not too long ago, my team was logging application data to a SQL Server owned and managed by IT (...
2
votes
2answers
31 views
How to find exact string from a sentence
I'm using SQLITE and I'm trying to find the exact word on a sentence, but I'm not getting just the word I'm getting for instance if I'm looking for Water I'm getting also aWater, wateri24 and that's ...
0
votes
1answer
27 views
Listing IDs that match values in two columns of a table
I'm using SQLite 3.27.2-3 on Debian Buster/stable. I could use some other DB, but for my use case (LuaTeX), something with low overhead seems to make sense.
My use case is that I have a letter (in ...
0
votes
3answers
29 views
SQLite: Leave only n rows for each field
I have a table
+-----------------------------+---------------+-----------+
| Painter | Painting | Rate |
+-----------------------------+---------------+-----------+
| ...
1
vote
2answers
28 views
How to set “PRAGMA foreign_keys = ON” each time I'm connecting to sqlite db via Datagrip
In my pet project I'm working with small sqlite db and I've just came to realization that, in order to ON DELETE CASCADE actually do the thing, I need to set PRAGMA foreign_keys = ON each time I'm ...
1
vote
1answer
34 views
Is there any advantage to putting some columns in another table, or should I just put them in the same table?
I have a SQLite database with about 50 tables in it. The largest table contains location data and it has 6,000,000 rows.
An example of some of the columns in this table are:
location_contrivedkey --(...
1
vote
3answers
51 views
Split Multiple Columns Into Multiple Columns by Delimiter
I would like to split multiple columns into multiple columns by a delimiter. I am using SQLite, but I am open to importing into another DBMS, if necessary.
An example table, showing how the table ...
0
votes
0answers
33 views
Common way to handle a changing reference in dependency of status
I am new to SQL an SQLite, so this is probably a silly question.
I am designing the data model for a commercial application. While documents (like quotes) are being created, I need to reference to ...
1
vote
2answers
54 views
Deduplication/normalization of string column on insert
I have a database of text tuples. Imagine e.g. full file paths + standard comments. Then you dump large file tree and generate comments for files. A file can have lots of comments, but their text ...
0
votes
0answers
33 views
Laying out the database schema for an interactive novel
I'm trying to create an interactive story telegram bot on Node-red. To store the story I'm using SQLite.
How it works: Bot shows you a piece of the story, then shows some answers (buttons) regarding ...
0
votes
1answer
27 views
Updating a column in one table based on matches with column in another table
I have 2 tables say table1 containing says 3 columns col1, col2 and col3 and table2 which contains col1 and col2. I want to set col3 = 'some value' where table1.col1 = table2.col1 and table1.col2 = ...
0
votes
1answer
13 views
How to AUTOINCREMENT against another column rather than the whole table in sqlite?
I'm using a table to track messages sent in several different channels.
I'd like to be able to track the index of each message on a per-channel basis. 'index' is a forbidden word in SQLite so here I'...
1
vote
1answer
38 views
How to insert values to a table from 2 foreign keys
Hey guys was wanting some help. I want to add the same value for Student_ID that is in the Studentinfo table in the GradeInfo and also wanted to add the same value for CourseNo that is in the ...
0
votes
1answer
32 views
What is the correct way to select rows from either side of a bridge table?
I want to do an SQL query for three columns. The database has five tables, two of which are bridge tables for the other three. When I attempt to select from the tables, I get a very large number of ...
0
votes
0answers
37 views
How to deal with refrencing a foreign key to two tables?
I have an interesting situation where I am making a data base off someone else's design that has to be as rule complaint as possible.
The issue I have is with the section shown above as it is a sub-...
0
votes
3answers
46 views
Usage of aggregate function with when self-joining a table
I want to find people from a database who has a weight bigger then the average weight:
SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)
But I have an error:
Result: misuse of ...
0
votes
1answer
18 views
Looping over column names in SQLite and running a command for each column name
I'm trying to run the following command
UPDATE attachments SET colname=TRIM(colname);
on every column name in a SQLite table.
I found the following approach to getting a list of column names in
an ...
1
vote
1answer
20 views
Which part of a DB bulk insert takes the most time?
One of the most common tip with regards to DB writing is to group your inserts and execute them as a single transaction. My question is, which part of the [begin transaction] - insert - [commit ...
2
votes
1answer
65 views
How to design a parent-child relationship that includes child priority/order
I am trying to design a table with a string column in SQLite3, I believe this is necessary to connect the tables parent to child, which represent entity types that are involved in a connection with a ...
0
votes
1answer
16 views
SQLite return sum of duplicate rows based on array
If I have a simple table fruits that looks like this:
+---+---------+------+
| | name | lbs |
+---+---------+------+
| 1 | apple | 0.25 |
| 2 | melon | 3.00 |
+---+---------+------+
In a ...
0
votes
1answer
21 views
SELECT column for a row only if its value is not empty
I want to display a certain column only if its value is not empty.
Pseudo code:
SELECT column1, IF_NOT_EMPTY(column2), ... FROM table;
Will display all columns except column2 if column2 is empty.
-1
votes
1answer
25 views
MySql data export to import in sqlite [closed]
This question has been asked a few times. However the links all lead to how to export the db such as schema and import that into sqlite. My scenario I already have an sqllite db with the tables. I ...
0
votes
1answer
19 views
What kind of a lock does IN keyword takes in SQL
If I have a query in which I have used IN keyword than what kind of lock will be taken by IN keyword.
Query is :
UPDATE table1 SET b_id where id in (SELECT id from table1 where id IS NULL )
I ...
0
votes
0answers
25 views
Getting Database is locked error [duplicate]
I have a query in which I am updating a table and have used IN keyword in the query. The query is something like this:
UPDATE q
SET b_id = 123
WHERE id IN (SELECT id FROM q WHERE b_id IS NULL ...
1
vote
2answers
94 views
sqlite Json grouping json_each
I want to generate json grouping users by year.
I have got a "user" table in sqlite with the following columns:
date
name
surname
value
I would like to create a JSON with the following structure:
...
1
vote
1answer
40 views
Multiple concurrent connections to same large SQLite file on a network share
I have a 7GB SQLite file and I am running multiple queries on it from different virtual machines in the cloud. Some of the queries are running in Python using the built in sqlite3 package, and others ...
1
vote
1answer
48 views
Top 10,20,30 in Group
Morning all! I have a program and database I created that pulls down data from a website (player cost for MLB teams for a game) and I aggregate that data and use it for various things.
One of the ...
0
votes
0answers
11 views
Want to be able to use sqlite to organize photos so that they can be selected by who is in the photograh
I want to create an sqlite db that has photograhs of a band's various gigs they have played at.
I want to be able to search the database by event,but also (and more importantly) by who is in the ...
0
votes
1answer
29 views
How to multi-match two tables efficiently?
I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both ...
0
votes
1answer
92 views
Overhead of triggers and foreign key constraints?
I have a sqlite database which is accessed by two programs. There are a number of invariants/constraints which should always hold. In addition to the usual foreign key constrains between tables, ...
0
votes
1answer
27 views
sqlite3 command does not save database after creation?
I'm migrating from MySQL. I'm having trouble scripting a SQLite database creation. Similar code worked well for MySQL.
It appears the database and tables are created, but then sqlite3 command does ...
0
votes
1answer
186 views
Comparing multiple tables in Sqlite 3 using python:
I am quite new to SQLITE3 as well as python. I am working on a project where I have one database with about 20 tables inside of it. One table is for user input and the other tables are pre-loaded with ...
0
votes
0answers
24 views
NoSQL big DB retrieve performance
A question about retrieving performance for simple but bug key-value DBs on python3.
I am working on a pipeline to compare several entries based on key-values (genetic variants found in patients with ...
0
votes
1answer
53 views
Group json data in db using a query
I have a db whose values are as follows:img Can I call the json value of listing_id(23,27....so on) in properties column(with having row value as "listing") using a single query, I would Really ...
1
vote
1answer
39 views
How to correctly use group by for getting the record with the closest time for each rec_id
Background
I have a table records which stores rec_id, rec_time and rec_value. A record is stored for each rec_id only when its rec_value changes. I would like to query the value for a bunch of ...
5
votes
1answer
59 views
Why an incorrect JOIN using correlated sub-query is so much slower
I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
1
vote
1answer
31 views
SQLITE: Select row ID based on only when the column all the values are same
I have a table where more than one rows with same ttid. I would like to get ttid rows based on status column is done.
tb tbid ttid status
-----------------------------
216 6 5 done
216 6 ...
0
votes
0answers
99 views
How to ignore timezone in sqlite?
I have a table:
CREATE TABLE "date" (
"datetime" TEXT,
PRIMARY KEY("datetime")
);
and rows:
datetime
--------
2019-02-01 01:30+03:00
2019-02-01 02:30:10-03:00
...
For example, my timezone ...
1
vote
1answer
365 views
SQLITE: Multiple foreign key referenced to multiple table cascade delete
In SQLite I would like to delete parent table row and it has to delete all the related child tables. I have gone through all the questions in StackExchange and other websites but my query no where ...
0
votes
1answer
27 views
How to denormalize many-to-many relation without nested select?
I have the following schema:
house has many students, student has one house
gender has many students, student has one gender
game has many students. student has many games
For instance, consider
...
1
vote
1answer
259 views
how to filter the resulted table in dbeaver?
I have big sqlite table that I filtered one column with IN statement from the GUI. It worked will but I want to filter another second column also based on the first selected. eg full table looks like.
...
1
vote
2answers
46 views
Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries
I am facing the following problem:
I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country ...
-2
votes
1answer
36 views
How to use substr a column and fetch value between limits
Let assume that there is a table as Sample and Name, Country, UnitID, HS column of type as text.
-- Table
CREATE TABLE #TempTable (
Name text,
UnitID text,
Country text,
HS text
)
...
0
votes
2answers
70 views
How to SELECT last row of every hour?
I have a table (in database) with one minute data in each row.
Like this:
Date_time Value
2018-11-01 10:58:00 189.0
2018-11-01 10:59:00 189.0
2018-11-01 11:00:00 189.19
2018-11-01 11:...
1
vote
1answer
35 views
How to change date format using SQL?
I have column with strings of dates with format: "dd.mm.yyyy".
I need to convert the date format contained in the strings to ISO8601 format: "YYYY-MM-DD" for the whole column.
1
vote
1answer
31 views
Constraint via Foreign Key
I have the following schema in SQLite:
CREATE TABLE calendars
(
id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);
CREATE TABLE entry_types
(
id INTEGER PRIMARY ...
0
votes
1answer
30 views
How to import SQL file which has no N prefix
I have a large .sql file in Persian Language. The file is exported from sqlite db browser and has no N prefix for unicode fields. How can I reuse it in Sql Server?
This is sample of insert query in ...
0
votes
0answers
35 views
SQLITE PHP Like Query wont return results on all rows
I have created a SQLite query in PHP which queries user input and checks it against the database, however when I run the query it just displays everything in the database. and if I was trying to ...
0
votes
1answer
176 views
Relation between Faculty, Student and Course tables [closed]
What should be the relation between following tables. Confuse whether i create an enroll table with student and course or courses_assigned table using faculty and course and then create an enroll ...
