The "ALTER TABLE" is a SQL statement that allows you to make schema changes to a database table (i.e. add, drop or alter columns from an existing table).
Questions tagged [alter-table]
1118 questions
1569
votes
32 answers
How can I rename a database column in a Ruby on Rails migration?
I wrongly named a column hased_password instead of hashed_password.
How do I update the database schema, using migration to rename this column?
user1994764
- 15,889
- 5
- 19
- 11
1360
votes
14 answers
Altering a column: null to not null
I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must be preserved.
I am looking…
Karmic Coder
- 17,569
- 6
- 32
- 42
796
votes
11 answers
Rename column SQL Server 2008
I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL.
ALTER TABLE table_name RENAME COLUMN old_name to new_name;
This statement doesn't work.
Serhio g. Lazin
- 9,442
- 6
- 25
- 33
783
votes
16 answers
Check if a temporary table exists and delete if it exists before creating a temporary table
I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, it will give an error saying "invalid column".…
Sridhar
- 8,874
- 4
- 26
- 37
557
votes
19 answers
Adding an identity to an existing column
I need to change the primary key of a table to an identity column, and there's already a number of rows in table.
I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.
What's the SQL…
Kirschstein
- 14,570
- 14
- 61
- 79
510
votes
9 answers
How to delete a column from a table in MySQL
Given the table created using:
CREATE TABLE tbl_Country
(
CountryId INT NOT NULL AUTO_INCREMENT,
IsDeleted bit,
PRIMARY KEY (CountryId)
)
How can I delete the column IsDeleted?
raji
- 5,265
- 2
- 13
- 4
479
votes
11 answers
Adding multiple columns AFTER a specific column in MySQL
I need to add multiple columns to a table but position the columns after a column called lastname.
I have tried this:
ALTER TABLE `users` ADD COLUMN
(
`count` smallint(6) NOT NULL,
`log` varchar(12) NOT NULL,
`status` int(10) unsigned…
Koala
- 5,253
- 4
- 25
- 34
464
votes
7 answers
How to rename a table in SQL Server?
The SQL query that I have used is :
ALTER TABLE oldtable RENAME TO newtable;
But, it gives me an error.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TO'.
Switch
- 14,783
- 21
- 69
- 110
443
votes
9 answers
Error renaming a column in MySQL
How do I rename a column in table xyz? The columns are:
Manufacurerid, name, status, AI, PK, int
I want to rename to manufacturerid
I tried using PHPMyAdmin panel, but I get this error:
MySQL said: Documentation
#1025 - Error on rename of…
Bharanikumar
- 25,457
- 50
- 131
- 201
405
votes
12 answers
How do you change the datatype of a column in SQL Server?
I am trying to change a column from a varchar(50) to a nvarchar(200). What is the SQL command to alter this table?
Ascalonian
- 14,409
- 18
- 71
- 103
374
votes
10 answers
Altering column size in SQL Server
How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)
Sreedhar Danturthi
- 7,119
- 19
- 68
- 111
336
votes
8 answers
How can I add a column that doesn't allow nulls in a Postgresql database?
I'm adding a new, "NOT NULL" column to my Postgresql database using the following query (sanitized for the Internet):
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL;
Each time I run this query, I receive the following error…
Huuuze
- 15,528
- 25
- 72
- 91
328
votes
16 answers
How do I rename a column in a SQLite database table?
I would need to rename a few columns in some tables in a SQLite database.
I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was not mentioned.
From the SQLite…
joce
- 9,624
- 19
- 56
- 74
324
votes
16 answers
SQL Server add auto increment primary key to existing table
As the title, I have an existing table which is already populated with 150000 records. I have added an Id column (which is currently null).
I'm assuming I can run a query to fill this column with incremental numbers, and then set as primary key and…
fearofawhackplanet
- 52,166
- 53
- 160
- 253
319
votes
3 answers
How can I modify the size of column in a MySQL table?
I have created a table and accidentally put varchar length as 300 instead of 65353. How can I fix that?
An example would be appreciated.
vehomzzz
- 42,832
- 72
- 186
- 216