Stack Overflow
Stack Overflow

Questions tagged [alter]

'alter' is a SQL keyword used to change or modify the table structure or the records in a table.

835 questions
522
votes
6 answers

How do I modify a MySQL column to allow NULL?

MySQL 5.0.45 What is the syntax to alter a table to allow a column to be null, alternately what's wrong with this: ALTER mytable MODIFY mycolumn varchar(255) null; I interpreted the manual as just run the above and it would recreate the column,…
zmf
  • 9,095
  • 2
  • 26
  • 28
382
votes
12 answers

How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?

I would like to write a single SQL command to drop multiple columns from a single table in one ALTER TABLE statement. From MSDN's ALTER TABLE documentation... DROP { [CONSTRAINT] constraint_name | COLUMN column_name } Specifies that…
Jesse Webb
  • 43,135
  • 27
  • 106
  • 143
300
votes
12 answers

How to remove constraints from my MySQL table?

I want to remove constraints from my table. My query is: ALTER TABLE `tbl_magazine_issue` DROP CONSTRAINT `FK_tbl_magazine_issue_mst_users` But I got an error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to…
deepu sankar
  • 4,335
  • 3
  • 26
  • 37
247
votes
6 answers

How to move columns in a MySQL table?

Currently I am having the following MySQL table: Employees (empID, empName, department); I want to change the table to the following: Employees (empID, department, empName); How can this be done using ALTER statements? Note: I want to change only…
sumit
  • 10,935
  • 24
  • 65
  • 83
223
votes
11 answers

How to change column datatype in SQL Server database without losing data?

I have SQL Server database and I just realized that I can change the type of one of the columns from int to bool. How can I do that without losing the data that is already entered into that table?
Ivan Stoyanov
  • 5,412
  • 12
  • 55
  • 71
114
votes
9 answers

How to add new column to MYSQL table?

I am trying to add a new column to my MYSQL table using PHP. I am unsure how to alter my table so that the new column is created. In my assessment table I have: assessmentid | q1 | q2 | q3 | q4 | q5 Say I have a page with a textbox and I type q6…
Steven Trainor
  • 1,255
  • 4
  • 13
  • 20
103
votes
4 answers

Trying to modify a constraint in PostgreSQL

I have checked the documentation provided by Oracle and found a way to modify a constraint without dropping the table. Problem is, it errors out at modify as it does not recognize the keyword. Using EMS SQL Manager for PostgreSQL. Alter table…
MISMajorDeveloperAnyways
  • 1,359
  • 3
  • 14
  • 20
98
votes
6 answers

ALTER TABLE ADD COLUMN takes a long time

I was just trying to add a column called "location" to a table (main_table) in a database. The command I run was ALTER TABLE main_table ADD COLUMN location varchar (256); The main_table contains > 2,000,000 rows. It keeps running for more than 2…
fanchyna
  • 2,623
  • 7
  • 36
  • 38
89
votes
1 answer

Updating integer column with null values in postgres

I would like to update my column with other column in other table. Before doing so, I would like to nullify my column(integer) first. However, below code did not work. (column_a: bigint; column_b: text) UPDATE table1 SET column_a IS NULL WHERE…
no_name
  • 1,083
  • 1
  • 8
  • 12
71
votes
4 answers

How can I alter this computed column in SQL Server 2008?

I have a computed column created with the following line: alter table tbPedidos add restricoes as (cast(case when restricaoLicenca = 1 or restricaoLote = 1 then 1 else 0 end as bit)) But, now I need to change this column for something like: alter…
André Miranda
  • 6,420
  • 20
  • 70
  • 94
66
votes
4 answers

SQL Server: how to write an alter index statement to add a column to the unique index?

I have a UNIQUE, NON CLUSTERED index on a table that is currently using 4 columns for the index. I want to create an alter script that can merely add another column to this index. The new column type is varchar. The database is SQL Server 2005.
JL.
  • 78,954
  • 126
  • 311
  • 459
62
votes
2 answers

How do I alter a mysql table column defaults?

I have a table with a column of type timestamp which defaults current_timestamp and updates to current_timestamp on every update. I want to remove the "on update" feature on this column. How do I write the alter statement? I tried the following: …
Tihom
  • 3,384
  • 6
  • 36
  • 47
54
votes
5 answers

Optimize mySql for faster alter table add column

I have a table that has 170,002,225 rows with about 35 columns and two indexes. I want to add a column. The alter table command took about 10 hours. Neither the processor seemed busy during that time nor were there excessive IO waits. This is on…
Andrew
  • 2,355
  • 4
  • 29
  • 42
53
votes
2 answers

Postgresql - Using subqueries with alter sequence expressions

Is it possible to use subqueries within alter expressions in PostgreSQL? I want to alter a sequence value based on a primary key column value. I tried using the following expression, but it wouldn't execute. alter sequence public.sequenceX restart…
Danmaxis
  • 1,710
  • 4
  • 17
  • 27
44
votes
6 answers

How to change all the tables in my database to UTF8 character set?

My database is not in UTF8, and I'd like to convert all the tables to UTF8, how can I do this?
nubela
  • 1
  • 24
  • 75
  • 123
1
2 3
…
55 56