Sqlite Drop Column: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
(6 intermediate revisions by 5 users not shown) | |||
Line 1: | Line 1: | ||
'''Q:''' How can I drop a column in sqlite-databases? | '''Q:''' How can I drop a column in sqlite-databases? | ||
'''A:''' It is not possible to drop columns within sqlite using the syntax ALTER TABLE DROP COLUMN [1]. However, there is a workaround for this issue [2]: | 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 [1]. However, there is a workaround for this issue [2]: | |||
<pre> | <pre> | ||
Line 14: | Line 16: | ||
</pre> | </pre> | ||
It's obvious that this is a little bit complex. But if you use sqlitebrowser [3] this can be done with a few mouse-clicks (look under -->modify table for a button "remove field". | It's obvious that this is a little bit complex. But if you use sqlitebrowser [3] 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 [4], which is similar to SqliteBrowser. | ||
Alternatively, you could try this approach, which is slightly fewer steps: | |||
<pre> | |||
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; | |||
</pre> | |||
* [1] http://www.sqlite.org/omitted.html | * [1] http://www.sqlite.org/omitted.html | ||
* [2] http://www.sqlite.org/faq.html# | * [2] http://www.sqlite.org/faq.html#q11 | ||
* [3] http://sqlitebrowser.sourceforge.net/ | * [3] http://sqlitebrowser.sourceforge.net/ | ||
* [4] https://addons.mozilla.org/en-US/firefox/addon/5817 | |||
[[Category:FAQ]] | [[Category:FAQ]] |
Latest revision as of 21: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 [1]. However, there is a workaround for this issue [2]:
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 [3] 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 [4], 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;