How to delete many rows in a big table, but not all the rows ( truncate table can't be used in this case )

[claude]

Hi All,

I want to delete many rows in a big table, but not all the rows ( truncate table can't be used in this case )

This table have to continue to be updated during operation ( because it's used by application).

That's means I can't move all data in a new table to truncate the table, then insert only data which are necessary.

So, is it possible to delete many rows in a big table without log ?

Options from database are :

select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log fullSybase version : 15.7 SMP SP101

Thanks in adavance

Regards,

Claude

[former_member350489]

How about selecting only the few rows you want to keep and insert them into a new table (exact same DDL but a different name), then drop the old table and rename the new one to the original table name that you dropped ?

[Former Member]

for the above solution you could use the "select into" it creates the same table ddl

rename the old table (SyBooks Online (Archive))

select *

into newtable

from oldtable

where conditions

were conditions meet the data you want to keep.

don't forget about indexes, etc (alternetively you could use ddlgen: SyBooks Online)

[Former Member]

Hank's example shows a good practice when deleting large amount of records in general. But it's probably not answering your question -- do some reading on this command:

set dml_logging minimal

it may be what you're looking for. You'll have to have a good understanding of the implications of doing so.

[Former Member]

This is one of the most asked questions for ASE. Answer: use small batch size.

Here is the sample code.

declare @errorStatus int, @rowsProcessed int

set rowcount xxx

select @rowsProcessed = 1
while (@rowsProcessed != 0)
begin
  begin tran

    delete ...

      from ...

     where ...

    select @rowsProcessed = @@rowcount, @errorStatus = @@error

    if (@errorStatus != 0)
    begin
       raiserror ....
       rollback tran
       return -1
    end
  commit tran

  -- please add "dump tran" here if "trunc" option is not on
end

set rowcount 0