Home » SQL & PL/SQL » SQL & PL/SQL » How to delete rows in column ?
How to delete rows in column ? [message #218988] Mon, 12 February 2007 07:01 Go to next message
esiole
Messages: 3
Registered: January 2007
Junior Member
Hello all

I have

TABLE 1
=======
col1
col2
col3
coln

How to delete all rows in col1, col2, coln ? (i update them on next step with an import from another database)

It seems so simple that i don't know how to do this.

Best regards.

[Updated on: Mon, 12 February 2007 07:33]

Report message to a moderator

Re: How to delete rows in column ? [message #218999 is a reply to message #218988] Mon, 12 February 2007 08:06 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
You don't delete individual columns in an RDBMS. You either delete a row or you don't. Likewise with insert.

But you can update columns and set them to null. Or you can update them with values from your other database as you suggest.
Re: How to delete rows in column ? [message #219014 is a reply to message #218988] Mon, 12 February 2007 10:21 Go to previous messageGo to next message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
Here is the syntax.

Delete table <tablename>;
commit;

or

Truncate table <tablename>;

Trucate will be faster , but you cannot retrieve data back. But during delete if you found wrong you can do a rollback before committing the transaction.

Thanks
Panneer Selvam
Re: How to delete rows in column ? [message #219154 is a reply to message #219014] Tue, 13 February 2007 03:18 Go to previous messageGo to next message
esiole
Messages: 3
Registered: January 2007
Junior Member
It is not a full delete i want to do, but partial.

In fact, Oracle don't permit this, you must rename table, create table with the fields you need to be updated, alter table with fields you want to keep from old table, and update fields.

It is not so simple that i'm thought. It is a good learning exercise for me.

Thanks for your help.
Re: How to delete rows in column ? [message #219165 is a reply to message #218988] Tue, 13 February 2007 08:08 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Depending on your situation, you can also potentially use a view.
Re: How to delete rows in column ? [message #219166 is a reply to message #218988] Tue, 13 February 2007 08:11 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Or wait...if you want to remove an actual column, you can indeed do that (likewise you can add a column).

MYDBA@orcl > create table test(a number, b number);

Table created.

MYDBA@orcl > desc test;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- -----------
 A                                                                          NUMBER
 B                                                                          NUMBER

MYDBA@orcl > alter table test drop (a);

Table altered.

MYDBA@orcl > desc test;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- -----------
 B                                                                          NUMBER

Re: How to delete rows in column ? [message #219207 is a reply to message #218988] Tue, 13 February 2007 10:13 Go to previous messageGo to next message
j0zele
Messages: 15
Registered: December 2006
Junior Member
if you want to set null (delete??) all rows in a column and not drop a column:

update table1 set col1 = null;

i think you dont want to drop, i understand that you want to delete all values in a column...

if you want to delete all values in the table then

delete table1;

you will not drop any column, but you will delete all values in.
Re: How to delete rows in column ? [message #219534 is a reply to message #218988] Wed, 14 February 2007 15:09 Go to previous messageGo to next message
IT Guru
Messages: 59
Registered: January 2007
Member
DELETE <tablename>

delete row from table but does not relse disk space.

Q1....belive internaly may be marked for delete & become non-accessable. is there any way to retrive it back even after commite is issue.

Q2........... Delete does not release disk space. How to reclaim disk space back of deleted record.
Re: How to delete rows in column ? [message #219604 is a reply to message #219534] Thu, 15 February 2007 02:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
IT Guru wrote on Wed, 14 February 2007 22:09
Q1....belive internaly may be marked for delete & become non-accessable. is there any way to retrive it back even after commite is issue.
A delete does physically delete the rows, but if the information is still available in the undo you can flashback a table.

IT Guru wrote on Wed, 14 February 2007 22:09
Q2........... Delete does not release disk space. How to reclaim disk space back of deleted record.

The deletion of records will, indeed not reset the high water mark. A TRUNCATE, on the other hand, will. But a truncate has some implications:
- it is DDL: Rollback possible
- it is no DML: no undo unformation. Flashback is not possible
- it is unconditional. You cannot add a where clause.

About space, it is not my cup of tea (I'm no DBA) but in 10g you can shrink a table by using the ALTER TABLE ... SHRINK SPACE clause. Perhaps the DBA's here have a more efficient way of reclaiming space.

MHE

[Updated on: Thu, 15 February 2007 02:10]

Report message to a moderator

Re: How to delete rows in column ? [message #219638 is a reply to message #219534] Thu, 15 February 2007 04:28 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Q2........... Delete does not release disk space. How to reclaim disk space back of deleted record.

This is only correct for certain, quite specific values of 'correct'

If you do a 'DELETE <table>' then none of the extents allocated to that table will be de-allocated, and the high watermark will not change, so the disk space will not be released back to the O/S

On the other hand, if you start inserting new data into that table, then the new rows will go into the space that was previously occupied by the old rows, rather than requiring the table to be extended. In this sense, the disk space was released back to Oracle to re-use for this table.
Previous Topic: Running a procedure on time
Next Topic: sum up columns
Goto Forum:
  


Current Time: Mon Dec 05 06:52:28 CST 2016

Total time taken to generate the page: 0.10816 seconds