SQLite Select Constant Text – Same as Column Name

sqlite

I'm trying to select const text for an insert. However the text is the same as a column name and gets interpreted as such. How do I work around this?

INSERT INTO Table1 (a, b, c)
SELECT "a", a, b 
FROM Table2

If Table2 looks like:

a, b
1, 1
2, 2

I want the inserted columns to look something like:

"a", 1, 1
"a", 2, 2

But with this SQL query, it interprets "a" as column a, which is clearly not what I want.

Best Answer

Values in SQLite should be wrapped in single-quotes, like:

INSERT INTO Table1 (a, b, c) 
SELECT 'a', a, b 
FROM Table2;

However, as @Ypercube asked in his comment, why do you want a column with the same value for every row?

Related Solutions

Select column from ALL tables

You're looking for UNION and UNION ALL.

select my_column from table1
union all
select my_column from table2
union all
select my_column from ...

But needing to select one column from every table is likely the sign of a significant design error.

SQLite – How to Select All Rows with a Minimum Value

As you have seen, a simple GROUP BY will not work because it would return only one record per group.

Your join works fine. For a large table, it will be efficient only if there is an index on the join columns (num and text).

Alternatively, you could use a correlated subquery:

SELECT *
FROM t
WHERE num = (SELECT MIN(num)
             FROM t AS t2
             WHERE t2.text = t.text);

SQLFiddle

When being executed, this query does not require a temporary table (your query does for the result of u), but will execute the subquery for each record in t, so text should be indexed. (Or use an index on both text and num to get a covering index.)

Best Answer

Related Solutions

Select column from ALL tables

SQLite – How to Select All Rows with a Minimum Value

Related Question