Jump to content
gcue

sqlite drop & rename column

Recommended Posts

hello world

i am trying to find out the syntax to rename a column or delete a column

I have tried syntax for mysql and oracle that i've found online - none work.

i am able to drop a table but not a column with this

_SQLite_Startup()
_SQLite_Open($s_DataBase_Path)

_SQLite_Exec(-1, "DROP TABLE aAssetInfo")

_SQLite_Close()
_SQLite_Shutdown()

but this doesnt work

_SQLite_Startup()
_SQLite_Open($s_DataBase_Path)

;rename
;~ _SQLite_Exec(-1, "ALTER TABLE aAssetInfo CHANGE Status Stats")  ;<<< doesnt work

;delete
_SQLite_Exec(-1, "ALTER TABLE aAssetInfo DROP COLUMN Status")  ;<<< doesnt work
;~ _SQLite_Exec(-1, "DROP COLUMN Status")  ;<<< doesnt work either

_SQLite_Close()
_SQLite_Shutdown()

any help is greatly appreciated!

Share this post


Link to post
Share on other sites
Posted (edited)

You can look at the information in the link below for the syntax on how to rename a column in a sqlite table:

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

 

You cannot delete a column, in sqlite, using an alter statement.  But you can rename the table, create a new table without the column(s), and then insert the values from the old table back into the new table by doing something like this:

-- Rename table
ALTER TABLE sites RENAME TO _sites_old;

-- Recreate new table without column(s)
-- (removed office_hours_end_time)
CREATE TABLE sites (
    "site_id" TEXT NOT NULL COLLATE NOCASE
    ,"name" TEXT NOT NULL COLLATE NOCASE
    ,"office_hours_start_time" TEXT
    ,PRIMARY KEY ("site_id")
    );

-- Insert values from old table back into new table
INSERT INTO sites (
    "site_id"
    ,"name"
    ,"office_hours_start_time"
    )
SELECT "site_id"
    ,"name"
    ,"office_hours_start_time"
FROM _sites_old;

 

Edited by TheXman

Share this post


Link to post
Share on other sites
Posted (edited)

thank you!!

used this to rename

_SQLite_SQLiteExe($s_DataBase_Path, "ALTER TABLE aTemp RENAME COLUMN 'Asset Tag' TO 'AssetTag'", $s_Out, $s_SQLITE3EXE_Path, True)

 

Edited by gcue

Share this post


Link to post
Share on other sites
Posted (edited)

when i drop the table, does it still exist in the database file?  reason is the file is huge - larger than the original.. (even though the new one has lesser columns than the original)

OR

is there a way to compact a sqlite database via command line?  i am able to compact it using the sqlitebrowser and the file size is significantly reduced

Edited by gcue

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...