Question on truncate and delete. 2

[EdRev]

I am a newbie on SQL (and Oracle for that matter)and I need help with the delete and truncate command. On the textbook I am referencing, the sample code for delete and truncate removes all records from a table.

delete tbl or truncate tbl.

Can I truncate or delete the rcords from a specific column?
I have this table structure Unit, Account, Jan-Dec and depending on the current month, I need to "clear" the data for that month. Can I do this with truncate or delete, or is there another command for this? Can I have a where clause on truncate?

Any help will be greatly appreciated.

 

[ek03]

You can't specify a where clause for a truncate, but you can for the delete command:
delete from tbl where month = 'dec'

 

[EdRev]

Thanks ek03, but jan-dec are column names.
Can I do this:

delete jan from tbl

 

[jimbopalmer]

almost sounds like you want to

update tbl set datacol = Null where period = 'DEC';

where datacol is the column you want to clear and period is the accounting period you want to clear, but I can't tell for sure from the problem as described I tried to remain child-like, all I acheived was childish.

 

[ek03]

I'm not sure what you want to do - are you looking for a way to set the data in one column to null? If so, you can do an update like jimbopalmer suggested - update tbl set Jan = null

 

[EdRev]

Thanks jimbopalmer!! I thnk that's where I'm going at.
With millions of records to evaluate and update, does it make a difference if I create a stored procedure to do this than to execute the sql on my script?

update tbl set jan=null where unit=strUnit and account=straccount;

 

[jimbopalmer]

if you write a procedure you may be able to commit for every depatment or every account, otherwise you will generate millions of rollback rows until you commit at the end.

other than that issue they should be the same I tried to remain child-like, all I acheived was childish.

 

[sem]

You may drop column at least since 8.1 :

alter table <tab> drop column <col> Regards, Dima

 

[EdRev]

Thanks Dima!! Question though, doesn't drop column &quot;deletes&quot; the column? which means it can't have a where clause in it?

 

[sem]

Yes, it's removed completely, from data dictionary. Regards, Dima

 

[THoey]

But then you can add the column back with null values in it... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...