Cara menggunakan delete column 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?

asked Dec 20, 2012 at 9:04

0

ALTER TABLE tbl_Country DROP COLUMN IsDeleted;

Here's a working example.

Note that the COLUMN keyword is optional, as MySQL will accept just DROP IsDeleted. Also, to drop multiple columns, you have to separate them by commas and include the DROP for each one.

ALTER TABLE tbl_Country
  DROP COLUMN IsDeleted,
  DROP COLUMN CountryName;

This allows you to DROP, ADD and ALTER multiple columns on the same table in the one statement. From the MySQL reference manual:

You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which permits only one of each clause per ALTER TABLE statement.

answered Dec 20, 2012 at 9:05

CynicalCynical

9,1251 gold badge14 silver badges30 bronze badges

0

Use ALTER TABLE with DROP COLUMN to drop a column from a table, and CHANGE or MODIFY to change a column.

ALTER TABLE tbl_Country DROP COLUMN IsDeleted;
ALTER TABLE tbl_Country MODIFY IsDeleted tinyint[1] NOT NULL;
ALTER TABLE tbl_Country CHANGE IsDeleted IsDeleted tinyint[1] NOT NULL;

Anthony

35.6k24 gold badges93 silver badges159 bronze badges

answered Dec 20, 2012 at 9:08

Saharsh ShahSaharsh Shah

28.3k8 gold badges46 silver badges82 bronze badges

1

To delete a single column:

ALTER TABLE `table1` DROP `column1`;

To delete multiple columns:

ALTER TABLE `table1`
DROP `column1`,
DROP `column2`,
DROP `column3`;

Pikamander2

6,4133 gold badges42 silver badges64 bronze badges

answered Dec 20, 2012 at 9:06

echo_Meecho_Me

36.8k5 gold badges57 silver badges78 bronze badges

1

You can use

alter table  drop column 

WAF

9853 gold badges14 silver badges31 bronze badges

answered Dec 20, 2012 at 9:07

Kapil gopinathKapil gopinath

1,0031 gold badge8 silver badges18 bronze badges

0

ALTER TABLE `tablename` DROP `columnname`;

Or,

ALTER TABLE `tablename` DROP COLUMN `columnname`;

Sam

7,15515 gold badges45 silver badges65 bronze badges

answered Dec 20, 2012 at 9:06

Avinash NairAvinash Nair

1,9242 gold badges13 silver badges17 bronze badges

If you are running MySQL 5.6 onwards, you can make this operation online, allowing other sessions to read and write to your table while the operation is been performed:

ALTER TABLE tbl_Country DROP COLUMN IsDeleted, ALGORITHM=INPLACE, LOCK=NONE;

answered Jun 8, 2018 at 14:37

A. ColonnaA. Colonna

8527 silver badges10 bronze badges

Use ALTER:

ALTER TABLE `tbl_Country` DROP COLUMN `column_name`;

answered Dec 20, 2012 at 9:06

Lo JuegoLo Juego

1,2959 silver badges12 bronze badges

ALTER TABLE tbl_Country DROP columnName;

Sam

7,15515 gold badges45 silver badges65 bronze badges

answered Dec 20, 2012 at 9:06

Sterling ArcherSterling Archer

21.5k17 gold badges80 silver badges113 bronze badges

0

It is worth mentioning that MySQL 8.0.23 and above supports Invisible Columns

CREATE TABLE tbl_Country[
  CountryId INT NOT NULL AUTO_INCREMENT,
  IsDeleted bit,
  PRIMARY KEY [CountryId] 
];

INSERT INTO tbl_Country VALUES [1, 1], [2,0];

ALTER TABLE tbl_Country ALTER COLUMN IsDeleted SET INVISIBLE;

SELECT * FROM tbl_Country;
CountryId
1
2

ALTER TABLE tbl_Country DROP COLUMN IsDeleted;

dbfiddle demo

It may be useful in scenarios when there is need to "hide" a column for a time being before it could be safely dropped[like reworking corresponding application/reports etc.].

answered Jan 24, 2021 at 22:43

Lukasz SzozdaLukasz Szozda

148k20 gold badges206 silver badges238 bronze badges