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.

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;

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

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';

Answer 4

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

Answer 5

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;

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

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

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.

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

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