SQLite: Copy all values from a previous row with one substitution.

Question

Hi y'all,

Using SQLite I need to copy nearly all of an existing row from a table, make a change to a single column, and insert the new row into the table. Something roughly like

INSERT INTO $tablename (c1, c2, ... , cn) 
    SELECT (c1, c2, ... , cn) FROM $tablenam

I tried substituting a some value ci into the the list of SELECT columns. This works if the value is a float or an integer, but not if it is a string. If the value is a string, SQLite interprets it as a column name, which naturally doesn't exist and I get an error.

I can write code to perform this operation, but I was hoping to do it in one query.

If of relevance, I am currently coding in tcl.

Thanks,

Sean

Answer 1

You mean this doesn't work?

INSERT INTO $tablename (c1, c2, ... , cn)
    SELECT (c1, c2, ..., "othervalue", ... , cn) FROM $tablename

How are you constructing your query?

pseudocode e.g. this won't work as othervalue is interpreted as columnname

dothquery("INSERT INTO $tablename (c1, c2, ... , cn)
        SELECT (c1, c2, ..., othervalue, ... , cn) FROM $tablename")

while this works, because the " around othervalue are included in escaped format and then sqllite should recognize it as expression not anymore as columnname

dothquery("INSERT INTO $tablename (c1, c2, ... , cn)
        SELECT (c1, c2, ..., \"othervalue\", ... , cn) FROM $tablename")

Answer 2

Make sure you have surrounded the string in 'singlequotes'. I find they work better for quoting strings in SQL queries than doublequotes. Make sure they are escaped too.

Answer 3

I don't know whether tcl supports parameterized queries or not. Using parameterized queries in sqlite has three advantages.

1. It prevents sql injection attacks.
2. You don't have to worry about ' and ". No more quoting of quotes or escaping of quotes.
3. It is faster because sqlite doesn't have to reparse every sql statement when you use parameters. (See this link for an performance example http://stackoverflow.com/questions/904796/how-do-i-get-around-the-problem-in-sqlite-and-c/926251#926251 ).