Looping over column names in SQLite and running a command for each column name

sqlite

I'm trying to run the following command

UPDATE attachments SET colname=TRIM(colname);

on every column name in a SQLite table.

I found the following approach to getting a list of column names in
an answer to "How to get a list of column names on sqlite3 / iPhone?", namely

SELECT name FROM PRAGMA_TABLE_INFO('your_table');

which seems like a nice, clean, simple way to do it. But this method is specific to SQLite, per PRAGMA Statements.

In any case, I'd like a solution that runs entirely in SQLite. I'm actually calling SQLite from Lua, using LuaSQL. So I could run the loop in Lua if necessary. But doing it inside SQLite just seems cleaner.

It would be even better if it's a general method that works for any RDBMS. But that may not be possible, and if it is, it would probably be more complicated

Best Answer

You can't do it in pure sqlite; column names have to be hardcoded in the query (also why they can't be bound to parameters), even if there was a way to use UPDATE in a loop, which there isn't.

Related Solutions

SQLite Trigger – Delete Existing Rows Before Inserting New Ones

Remember that this is a lite version of SQL and does not support all operations. See the following link:

https://www.sqlite.org/lang_createtrigger.html

SQLite supports a BEFORE TRIGGER which can delete rows before further action is taken. The link also provides some cautions about how that works:

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result of the subsequent update or delete operation is undefined. Furthermore, if a BEFORE trigger modifies or deletes a row, then it is undefined whether or not AFTER triggers that would have otherwise run on those rows will in fact run.

See the link to learn other details about the SQLite triggers.

Of course you could write your SQLite code to delete the rows that you no longer need in your database before inserting new rows into your database. I would prefer that approach over a trigger since your are just getting rid of unwanted data.

SQLite – Distinguish Column Values by ID Range and Count

For each lower boundary, search the matching upper boundary:

WITH uppers(rowid) AS (
  SELECT f.rowid FROM intersection f
  WHERE cnt = (SELECT MAX(cnt) FROM intersection)
  AND NOT EXISTS (
      SELECT * FROM intersection s
      WHERE s.cnt = f.cnt
      AND s.rowid = f.rowid+1)
)
SELECT f.rowid AS lb,
       (SELECT u.rowid FROM uppers u
        WHERE u.rowid >= f.rowid
        ORDER BY u.rowid ASC LIMIT 1
       ) ub
FROM intersection f
WHERE cnt = (SELECT MAX(cnt) FROM intersection)
AND NOT EXISTS (
    SELECT * FROM intersection s
    WHERE s.cnt = f.cnt
    AND s.rowid = f.rowid-1);

lb  ub
--- ---
106 113
152 161

These values can then be used for BETWEEN:

WITH uppers(rowid) AS (
  SELECT f.rowid FROM intersection f
  WHERE cnt = (SELECT MAX(cnt) FROM intersection)
  AND NOT EXISTS (
      SELECT * FROM intersection s
      WHERE s.cnt = f.cnt
      AND s.rowid = f.rowid+1)
),
bounds(lb, ub) AS (
  SELECT f.rowid,
         (SELECT u.rowid FROM uppers u
          WHERE u.rowid >= f.rowid
          ORDER BY u.rowid ASC LIMIT 1)
  FROM intersection f
  WHERE cnt = (SELECT MAX(cnt) FROM intersection)
  AND NOT EXISTS (
      SELECT * FROM intersection s
      WHERE s.cnt = f.cnt
      AND s.rowid = f.rowid-1)
)
SELECT (SELECT count(*) FROM intersection
        WHERE rowid BETWEEN bounds.lb AND bounds.ub
       ) AS count,
       (SELECT avg(sweep) FROM intersection
        WHERE rowid BETWEEN bounds.lb AND bounds.ub
       ) AS avg_sweep
FROM bounds;

Best Answer

Related Solutions

SQLite Trigger – Delete Existing Rows Before Inserting New Ones

SQLite – Distinguish Column Values by ID Range and Count

Related Question