Declare variable in SQLite and use it

Question

I want to declare a variable in SQLite and use it in insert operation.

Like in MS SQL:

declare @name as varchar(10)
set name = 'name'
select * from table where name = @name

For example, I will need to get last_insert_row and use it in insert.

I have found something about binding but I didn't really fully understood it.

Eyler

sqlite doesn't support this. – Stretcher

hope there's a better solution now - Aug 2018 – Hedonism

@Hedonism its 2022 now and i'm disappointed as well... – Majors

Answer 1

SQLite doesn't support native variable syntax, but you can achieve virtually the same using an in-memory temp table.

I've used the below approach for large projects and works like a charm.

    /* Create in-memory temp table for variables */
    BEGIN;

    PRAGMA temp_store = 2; /* 2 means use in-memory */
    CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);

    /* Declaring a variable */
    INSERT INTO _Variables (Name) VALUES ('VariableName');

    /* Assigning a variable (pick the right storage class) */
    UPDATE _Variables SET IntegerValue = ... WHERE Name = 'VariableName';

    /* Getting variable value (use within expression) */
    ... (SELECT coalesce(RealValue, IntegerValue, BlobValue, TextValue) FROM _Variables WHERE Name = 'VariableName' LIMIT 1) ...

    DROP TABLE _Variables;
    END;

Hardback

This works but there are a few remarks, I tried this on spatialite , and there it says you cannot change the temp store from within a transaction. Also, I think you are missing a semicolon after BEGIN. Tx for sharing this solution. – Nigrosine

How to increment this? I mean how to increment this variable as if it increments with sequential calls. – Clamatorial

Temporary tables are not guaranteed to be in-memory. That is dependent upon the compiler options and also the PRAGMA temp_store setting. In fact, according to online docs, the default setting is to store temporarily files to disk (which includes files for temporary tables and indices). – Guidance

Will a previously created trigger be able to reference this temp table? – Faitour

@CPerkins The docs you linked to say PRAGMA temp_store = 2 stores in memory. – Hardback

@HermanSchoenfeld Apparently I missed that line when I made the comment, but what I shared is still useful since it shared relevant documentation, and your original code has insufficient comments. I suggest adding an explicit comment exactly at the line of code (not just above the code block). Resolve the magic number by adding something like /* 2 means use in-memory */. – Guidance

better to always use IF EXISTS / NOT when creating or dropping to avoid errors: ( DROP TABLE IF EXISTS _Variables ) and (CREATE TEMP TABLE IF NOT EXISTS _Variables(.... ) – Fiske

Answer 2

For a read-only variable (that is, a constant value set once and used anywhere in the query), use a Common Table Expression (CTE).

WITH const AS (SELECT 'name' AS name, 10 AS more)
SELECT table.cost, (table.cost + const.more) AS newCost
FROM table, const 
WHERE table.name = const.name

SQLite WITH clause

Heldentenor

I don't know why but performance of my query suffers massively from this solution. It runs even x10 slower than with literals in place. – Codie

Answer 3

Herman's solution works, but it can be simplified because Sqlite allows to store any value type on any field.

Here is a simpler version that uses one Value field declared as TEXT to store any value:

CREATE TEMP TABLE IF NOT EXISTS Variables (Name TEXT PRIMARY KEY, Value TEXT);

INSERT OR REPLACE INTO Variables VALUES ('VarStr', 'Val1');
INSERT OR REPLACE INTO Variables VALUES ('VarInt', 123);
INSERT OR REPLACE INTO Variables VALUES ('VarBlob', x'12345678');

SELECT Value
  FROM Variables
 WHERE Name = 'VarStr'
UNION ALL
SELECT Value
  FROM Variables
 WHERE Name = 'VarInt'
UNION ALL
SELECT Value
  FROM Variables
 WHERE Name = 'VarBlob';

Clavicle

but you should not forget to cast the value to the right type if you want to use it in comparisons or you may get surprising results – Escurial

SQLite has an ANY type which you should prefer to mis-using a TEXT column. It's both clearer from a documentation / schma point of view, and it will work correctly with the recent STRICT tables. – Hooch

Answer 4

Herman's solution worked for me, but the ... had me mixed up for a bit. I'm including the demo I worked up based on his answer. The additional features in my answer include foreign key support, auto incrementing keys, and use of the last_insert_rowid() function to get the last auto generated key in a transaction.

My need for this information came up when I hit a transaction that required three foreign keys but I could only get the last one with last_insert_rowid().

PRAGMA foreign_keys = ON;   -- sqlite foreign key support is off by default
PRAGMA temp_store = 2;      -- store temp table in memory, not on disk

CREATE TABLE Foo(
    Thing1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);

CREATE TABLE Bar(
    Thing2 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    FOREIGN KEY(Thing2) REFERENCES Foo(Thing1)
);

BEGIN TRANSACTION;

CREATE TEMP TABLE _Variables(Key TEXT, Value INTEGER);

INSERT INTO Foo(Thing1)
VALUES(2);

INSERT INTO _Variables(Key, Value)
VALUES('FooThing', last_insert_rowid());

INSERT INTO Bar(Thing2)
VALUES((SELECT Value FROM _Variables WHERE Key = 'FooThing'));

DROP TABLE _Variables;

END TRANSACTION;

Vaishnava

Answer 5

To use the one from denverCR in your example:

WITH tblCTE AS (SELECT "Joe" AS namevar)
SELECT * FROM table, tblCTE
WHERE name = namevar

As a beginner I found other answers too difficult to understand, hope this works

Soakage

Answer 6

Creating "VARIABLE" for use in SQLite SELECT (and some other) statements

CREATE TEMP TABLE IF NOT EXISTS variable AS SELECT '2002' AS _year; --creating the "variable" named "_year" with value "2002"
UPDATE variable SET _year = '2021'; --changing the variable named "_year" assigning "new" value "2021"
SELECT _year FROM variable; --viewing the variable
SELECT 'TEST', (SELECT _year FROM variable) AS _year; --using the variable
SELECT taxyr FROM owndat WHERE taxyr = (SELECT _year FROM variable); --another example of using the variable
SELECT DISTINCT taxyr FROM owndat WHERE taxyr IN ('2022',(SELECT _year FROM variable)); --another example of using the variable
DROP TABLE IF EXISTS variable; --releasing the "variable" if needed to be released

Myrtia

Answer 7

After reading all the answers I prefer something like this:

select *
from table, (select 'name' as name) const
where table.name = const.name

Bound

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Archenemy

Answer 8

I frequently have to post-mortem analyse cases in a database. This involves running a series of queries filtering for the set of values, several times with a different set of values each time.

I found very useful to have a "variables table" with one column per "variable" needed, where I set the values I need each time, and having the series of queries not change every time.

PRAGMA temp_store = 2;      -- store temp table in memory, not on disk
CREATE TEMP TABLE IF NOT EXISTS _vars (_var1 INTEGER, _var2 INTEGER);

DELETE FROM _vars WHERE TRUE;       -- run this when changing set of values
INSERT OR REPLACE INTO  _vars VALUES (1492, 1112);       --new values
SELECT * FROM _vars;       -- check values are set and only one row is present

-- here comes the series of queries I need to run for each set of values
SELECT * 
FROM my_table_name  
WHERE one_column_name = (SELECT _var1 FROM _vars)
  AND other_column_name = (SELECT _var2 FROM _vars);

If you ensure your "variables" have unique names (that do not occur in the tables you are querying), you may simplify even more the queries:

SELECT * 
FROM my_table_name, _vars
WHERE one_column_name = _var1
  AND other_column_name = _var2;

When I finish with one set of values I go back to the DELETE and set a new set of values for my "variables" and keep going.

At the end, clean up by means of

DROP TABLE IF EXISTS _vars;

WARNING

Keep in mind that all of this is made while PRAGMA temp_store = 2; is in force, so any data creation will try to go on memory.

Also beware of database/connection changes. The tables created in memory are coupled with the active database or connection. If you change database (think "USE ") or in you database editor/GUI you change connection, the temporal tables in memory will not follow: you will have to create them again for the next database or connection. And that implies that if you are changing database to compare query results from more than one database, keep in mind that your queries are using the values from the tables in memory currently associated with the database you are using. It follows that if you change them to run a query in one database and then you change to run the same query in another database, you need change the values in memory before running the query or you will not be running the same query.

Laxity

Answer 9

Try using Binding Values. You cannot use variables as you do in T-SQL but you can use "parameters". I hope the following link is usefull.Binding Values

Netti

you can make your answer richer providing examples. Links can be moved but your examples will be here for future reference. – Calefactory

Answer 10

I found one solution for assign variables to COLUMN or TABLE:

conn = sqlite3.connect('database.db')
cursor=conn.cursor()
z="Cash_payers"   # bring results from Table 1 , Column: Customers and COLUMN 
# which are pays cash
sorgu_y= Customers #Column name
query1="SELECT  * FROM  Table_1 WHERE " +sorgu_y+ " LIKE ? "
print (query1)
query=(query1)
cursor.execute(query,(z,))

Don't forget input one space between the WHERE and double quotes and between the double quotes and LIKE

Dylane

I think the question was about plain SQL, it's obvious you can do it via python or any other programming language – Majors

That's correct. It is Python. – Dylane