Difference between revisions of "Sqlite Drop Column"
|Line 41:||Line 41:|
Latest revision as of 13:35, 30 December 2011
Q: How can I drop a column in sqlite-databases?
New A: GRASS 6.3.cvs contains v.db.dropcol which supports it.
Old A: It is not possible to drop columns within sqlite using the syntax ALTER TABLE DROP COLUMN . However, there is a workaround for this issue :
BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; DROP TABLE t1; CREATE TABLE t1(a,b); INSERT INTO t1 SELECT a,b FROM t1_backup; DROP TABLE t1_backup; COMMIT;
It's obvious that this is a little bit complex. But if you use sqlitebrowser  this can be done with a few mouse-clicks (look under -->modify table for a button "remove field". See also the Firefox SQLite Manager add-on , which is similar to SqliteBrowser.
Alternatively, you could try this approach, which is slightly fewer steps:
BEGIN TRANSACTION; CREATE TABLE t1_new ( foo TEXT PRIMARY KEY, bar TEXT, baz INTEGER ); INSERT INTO t1_new SELECT foo, bar, baz FROM t1; DROP TABLE t1; ALTER TABLE t1_new RENAME TO t1; COMMIT;