Difference between revisions of "Sqlite Drop Column"

From GRASS-Wiki
Jump to navigation Jump to search
 
m (Reverted edits by Reverse22 (talk) to last revision by Robbiebow)
 
(7 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#q13
* [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]]

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;