sqlite drop & rename column

[gcue]

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!

[TheXman]

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 April 1, 2019 by TheXman

[gcue]

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 April 2, 2019 by gcue

[gcue]

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 April 2, 2019 by gcue

[TheXman]

The answers to your questions can be found here:

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

 

[gcue]

thanks again!

[TheXman]

You're welcome