How Can I Simulate Variables in SQLite Inserts?

Simulating variables for INSERT statements in SQLite

SQLite, unlike MS SQL, does not support native variable syntax. However, you can use in-memory temporary tables to simulate similar functionality.

Create variable storage area

First, create an in-memory temporary table named "_Variables" to store your variables:

<code class="language-sql">BEGIN;
PRAGMA temp_store = 2; /* 使用内存存储 */
CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);</code>

Declare variables

Declare a variable named "VariableName" by inserting the variable name into the "_Variables" table:

<code class="language-sql">INSERT INTO _Variables (Name) VALUES ('VariableName');</code>

Assign a value to a variable

Assign values ​​to your variables. In this example, we will assign it an integer:

<code class="language-sql">UPDATE _Variables SET IntegerValue = 42 WHERE Name = 'VariableName';</code>

Using variables in INSERT statements

You can now use the value assigned to a variable in an INSERT operation. In the following expression, the variable "VariableName" is used in the WHERE clause:

<code class="language-sql">INSERT INTO Table1 (Column1, Column2)
SELECT Column1, Column2
FROM Table2
WHERE Column1 > (SELECT COALESCE(RealValue, IntegerValue, BlobValue, TextValue) FROM _Variables WHERE Name = 'VariableName' LIMIT 1);</code>

Close transaction

When you are done, don’t forget to close the transaction:

<code class="language-sql">DROP TABLE _Variables;
COMMIT;</code>

The above is the detailed content of How Can I Simulate Variables in SQLite Inserts?. For more information, please follow other related articles on the PHP Chinese website!