Deleting entire data from a particular column in oracle-sql

Question

Recently I have started learning Oracle-sql. I know that with the help of DELETE command we can delete a particular row(s). So, Is it possible to delete entire data from a particular column in a table using only DELETE command. (I know that using UPDATE command by setting null values to entire column we can achieve the functionality of DELETE).

`UPDATE table SET your_column_name = NULL` DELETE removes rows, alternatively you can get rid of column using `ALTER table DROP COLUMN name` (not recommended) — Lukasz Szozda, Sep 26 '15 at 13:43
@lad2025 Thank you. Now I clearly understood that DELETE removes only rows but not entire data from a particular column. — Rajeev, Sep 26 '15 at 13:53

score 26 · Accepted Answer · edited Apr 10 '18 at 13:42

26

DELETE

The DELETE statement removes entire rows of data from a specified table or view

If you want to "remove" data from particular column update it:

UPDATE table_name
SET your_column_name = NULL;

or if column is NOT NULL

UPDATE table_name
SET your_column_name = <value_indicating_removed_data>;

You can also remove entire column using DDL:

ALTER TABLE table_name DROP COLUMN column_name;

edited Apr 10 '18 at 13:42

Yahor M

617
8
8

answered Sep 26 '15 at 13:47

Lukasz Szozda

162,964
23
234
275

What do you mean by ?. – Rajeev Sep 26 '15 at 14:08
@RajeeVVenkaT For example your column is set to `NOT NULL` and you have column name, you can for example set ` ` empty string – Lukasz Szozda Sep 26 '15 at 14:21
I observed that a column when set NOT NULL cannot be updated with ' '. So, only by using DROP or UNUSED command we can perform actions. – Rajeev Sep 26 '15 at 15:02
Now I understood. Column when set NOT NULL cannot be updated with ' ' but can be updated with ' '. Small difference, but matters a lot. – Rajeev Sep 26 '15 at 15:39
1

@RajeeVVenkaT Yes, because Oracle treats internally empty string as NULL – Lukasz Szozda Sep 26 '15 at 15:40

Gordon Linoff · Answer 2 · 2015-09-26T14:03:51.380

2

In SQL, delete deletes rows not columns.

You have three options in Oracle:

Set all the values to NULL using update.
Remove the column from the table.
Set the column to unused.

The last two use alter table:

alter table t drop column col;
alter table t set unused (col);

edited Sep 26 '15 at 14:03

answered Sep 26 '15 at 13:46

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 3 · edited Jul 12 '17 at 14:36

2

Use Invisible Type, which is from an oracle 12cR2.

ALTER TABLE LOG1
MODIFY operation  INVISIBLE

It is a better than drop of a particular column.If you need to visible you can get back by altering with an VISIBLE of a column name.

edited Jul 12 '17 at 14:36

Shaminder Singh

1,283
2
18
31

answered Jul 12 '17 at 13:10

mAEHS

21
1

score -1 · Answer 4 · edited Mar 16 '18 at 07:00

-1

update employee set commission=nvl2(commission,'','')

this will remove all the data from the column

edited Mar 16 '18 at 07:00

MarmiK

5,639
6
40
49

answered Mar 16 '18 at 06:17

malik Shaikh

1

Deleting entire data from a particular column in oracle-sql

4 Answers4