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
11
votes
3 answers

SQL Server: alter table, how to add SPARSE definition

I would like alter my table and add SPARSE option to all fields that contain a lot of NULL values. What is the right syntax for this ALTER TABLE command?
jrara
  • 16,239
  • 33
  • 89
  • 120
11
votes
1 answer

how to drop a column if exists in mysql?

ALTER TABLE user DROP COLUMN registered_date We can drop a column using the above command. But how can i drop it only if the column registered_date exist?
CloudSeph
  • 863
  • 4
  • 15
  • 36
11
votes
5 answers

MySQL ALTER TABLE on very large table - is it safe to run it?

I have a MySQL database with a MyISAM table with 4 million rows. I update this table about once a week with about 2000 new rows. After updating, I then alter the table like this: ALTER TABLE x ORDER BY PK DESC I order the table by the primary key…
Timothy Mifsud
10
votes
3 answers

Oracle Alter command to rename existing Column errorring

alter table tablename rename column zl_divn_nbr to div_loc_nbr; Error while executing the above statement. Please help. SQL Error: ORA-54032: column to be renamed is used in a virtual column expression 54032. 0000 - "column to be renamed is used…
Prashanth
  • 109
  • 1
  • 5
10
votes
1 answer

Mysql alter comment column only

I'm wondering if it's possible to change only comment on a column in mysql without change the column definition data like name, type and key. Example: ALTER TABLE `user` CHANGE `id` `id` INT(11) COMMENT 'id of user' Can i change only the comment…
chaillouvincent
  • 197
  • 1
  • 3
  • 17
10
votes
3 answers

Removing enum values from mysql column

I have a table with a enum column called action. The permitted values currently are: act1,act2,act3,act4. I want act3 and act4 to be removed and my table's current state does not contain any rows with act3 or act4. When I'm trying to modify the…
mickeymoon
  • 4,820
  • 5
  • 31
  • 56
9
votes
3 answers

HibernateJpaVendorAdapter's generateDdl doesn't alter tables

I am developing a website using Spring+JPA+Hibernate. In the persistence configuration (JPA+Hibernate) I'm setting the HibernateJpaVendorAdapter's generateDdl attribute to true and in fact new entities correctly create the new table in the DB. Once…
satoshi
  • 3,963
  • 6
  • 46
  • 57
9
votes
2 answers

How to add new JSON column in Postgres

I am trying to add a json type column to the table, but it doesn’t work and I cannot get normal examples, what am I doing wrong? ALTER TABLE user ADD COLUMN purshased_product SET DATA TYPE JSONB USING purshased_product::JSONB; I'm not trying to…
Самир Шахмурадлы
  • 775
  • 2
  • 11
  • 23
8
votes
1 answer

Altering MySQL table column type from INT to BIGINT

I have a table with just under 50 million rows. It hit the limit for INT (2147483647). At the moment the table is not being written to. I am planning on changing the ID column from INT to BIGINT. I am using a Rails migration to do this with the…
Arthur
  • 1,970
  • 4
  • 18
  • 19
8
votes
1 answer

H2 add multiple column MySQL syntax

I have the following table: CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(256) ); I would like to add multiple columns in one query. I am using H2 a lot in testing but in production I use MySQL. Both are using same DDL…
Andrii Abramov
  • 10,019
  • 9
  • 74
  • 96
8
votes
3 answers

MySQL - How to modify column default value?

How do I change my column's default value from None to something else? For example, I want my dates to have a default value of 0000-00-00 if I don't specify one when I create the row. I understand this in phpMyAdmin, but I'm not sure how to do it…
User
  • 23,729
  • 38
  • 124
  • 207
8
votes
5 answers

Change mysql field name in a huge table

I have a table with 21 million row.I have to change one of the row name.When I try with the query "alter table company change id new_id int(11)"; query never ends. Is there a simple way to change big mysql table's field name?
demircan
  • 231
  • 4
  • 9
8
votes
3 answers

Altering the data type of a column in a HUGE table. Performance issues

I want to run this on my table: ALTER TABLE table_name MODIFY col_name VARCHAR(255) But my table is huge, it has more than 65M (65 million) rows. Now when I execute, it takes nearly 50mins to execute this command. Any better way to alter table?
Kiran
  • 147
  • 3
  • 10
7
votes
3 answers

Change column name without recreating the MySQL table

Is there a way to rename a column on an InnoDB table without a major alter? The table is pretty big and I want to avoid major downtime.
Ran
  • 3,455
  • 12
  • 47
  • 60
7
votes
3 answers

MySQL Alter table, add column with unique random value

I have a table that I added a column called phone - the table also has an id set as a primary key that auto_increments. How can I insert a random value into the phone column, that won't be duplicated. The following UPDATE statement did insert random…
Schoffelman
  • 1,806
  • 1
  • 20
  • 23
1 2 3
…
55 56