From documentation: Note that the table does not remain in this order after inserts and deletes Actually, if you issue SELECT … ORDER BY to this table, the option to ALTER TABLE won’t spare you of filesort, but instead make filesort much faster. Sorting an already ordered set is equivalent to browsing this set to … Read more
You might want to look at pt-online-schema-change from Percona toolkit. Essentially what it does is: Copies original table structure, runs ALTER. Copies rows from old table to newly created one. Uses triggers to track and sync changes while copying. When everything is finished it swaps tables by renaming both. Works very well for single instance … Read more
The only other option is to do manually what many RDBMS systems do anyway… – Create a new table You can then copy the contents of the old table over a chunk at a time. Whilst always being cautious of any INSERT/UPDATE/DELETE on the source table. (Could be managed by a trigger. Although this would … Read more
ALTER TABLE `table_name` MODIFY COLUMN `column_name2` enum( ‘existing_value1’, ‘existing_value2’, ‘new_value1’, ‘new_value2’ ) NOT NULL AFTER `column_name1`;
Support for DEFAULT was included in Access DDL with Jet 4 (Access 2000). However it can only be used in DDL executed from an ADO connection. This worked with Access 2007. CurrentProject.Connection.Execute “ALTER TABLE MyTable ” & _ “ALTER COLUMN field2 SET DEFAULT “”foo””;” Note if your db file is Access 97 or earlier, you … Read more
CHANGE COLUMN If you have already created your MySQL database, and decide after the fact that one of your columns is named incorrectly, you don’t need to remove it and make a replacement, you can simply rename it using change column. ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST; MODIFY COLUMN This … Read more
MySQL INFORMATION_SCHEMA database to the rescue: — First check if the table exists IF EXISTS(SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=”db_name” AND table_name LIKE ‘wild’) — If exists, retreive columns information from that table THEN SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=”tbl_name” AND table_schema=”db_name”; — do some action, i.e. ALTER TABLE if some columns … Read more
SQLite doesn’t support removing or modifying columns, apparently. But do remember that column data types aren’t rigid in SQLite, either. See also: SQLite Modify Column
Do you mean altering the table after it has been created? If so you need to use alter table, in particular: ALTER TABLE tablename MODIFY COLUMN new-column-definition e.g. ALTER TABLE test MODIFY COLUMN locationExpect VARCHAR(120);
You have to set a default value. ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL DEFAULT ‘foo’; … some work (set real values as you want)… ALTER TABLE mytable ALTER COLUMN mycolumn DROP DEFAULT;