05-15-2006 11:14 PM
Hi,
I need to delete data from table which contains millions of records, Iam assuming it will take hours and also resource, can any one tell me how to delete data not with much system resource.
Thanks
Raj
05-15-2006 11:21 PM
Delete statement should not take much time and resource unlike modify ,append.... You dont have any choice other than that
05-15-2006 11:31 PM
Furthermore, also see the link:
I dont want to repeat the same code again and again!! If youll search delete entries on SDN, youll find lot of threads
Hope thisll give you idea!!
<b>P.S award the points.</b>
Good luck
Thanks
Saquib Khan
"Some are wise and some are otherwise"
05-16-2006 12:22 AM
If u want to delete entire data better u use data base utility (se14)
05-16-2006 8:16 AM
Hai Raj Mitta
Delete from a database table
- DELETE FROM dbtab WHERE condition.
- DELETE FROM (dbtabname) WHERE condition.
- DELETE dbtab.
- DELETE *dbtab.
- DELETE (dbtabname) ... .
- DELETE dbtab FROM TABLE itab.
- DELETE (dbtabname) FROM TABLE itab.
- DELETE dbtab VERSION vers.
- DELETE *dbtab VERSION vers.
DELETE - Delete from a database table
Variants
1. DELETE FROM dbtab WHERE condition.
DELETE FROM (dbtabname) WHERE condition.
2. DELETE dbtab.
DELETE *dbtab.
DELETE (dbtabname) ...
3. DELETE dbtab FROM TABLE itab.
DELETE (dbtabname) FROM TABLE itab.
4. DELETE dbtab VERSION vers.
DELETE *dbtab VERSION vers.
Effect
Deletes lines in a database table . You can specify the name of the database table either in the program itself with DELETE FROM dbtab ... or at runtime as the contents of the field dbtabname with DELETE FROM (dbtabname) ... . In both cases, the database table must be known in the ABAP/4 Dictionary. If you specify the name in the program, there must also be an appropriate TABLES statement. Only data from the current client is usually deleted. You can delete data using a view only if the view refers to a single table and was created in the ABAP/4 Dictionary with the maintenance status "No restriction".
DELETE belongs to the Open SQL command set.
Note
The DELETE statement does not perform authorization checks : You must program these yourself.
Variant 1
DELETE FROM dbtab WHERE condition.
DELETE FROM (dbtabname) WHERE condition.
Addition
... CLIENT SPECIFIED
Effect
Deletes lines in a database table that satisfy the WHERE clause condition . With this variant, specification of a WHERE condition is obligatory .
When the statement has been executed, the system field SY-DBCNT contains the number of deleted lines.
The return code value is set as follows:
SY-SUBRC = 0 At least one line was deleted.
SY_SUBRC = 4 No lines were deleted, since no line was selected.
Example
Delete all bookings for the Lufthansa flight 0400 on 28.02.1995 (in the current client):
TABLES SBOOK.
DELETE FROM SBOOK WHERE CARRID = 'LH' AND
CONNID = '0400' AND
FLDATE = '19950228'.
Note
To delete all the lines in a table, you must specify a WHERE condition that is true for all lines. You can achieve this with
... WHERE f IN itab
If the internal table itab is empty, such a condition would select all lines.
Addition
... CLIENT SPECIFIED
Effect
Switches off automatic client handling. This allows you to delete data across all clients in the case of client-specific tables. The client field is then treated like a normal table field, for which you can formulate suitable conditions in the WHERE clause.
You must specify the addition CLIENT SPECIFIED immediately after the name of the database table.
Variant 2
DELETE dbtab.
DELETE *dbtab.
DELETE (dbtabname) ...
Additions
1. ... FROM wa
2. ... CLIENT SPECIFIED
Effect
These are SAP-specific short forms used to delete a single line of a database table. If the name of the database table is specified in the program, the primary key of the line to be deleted is taken from the specified work area - dbtab or *dbtab . If the name of the database table is not determined until runtime ( DELETE (dbtabname) ... ), the addition ... FROM wa is obligatory .
When the statement has been executed, the system field SY-DBCNT contains the number of deleted lines (0 or 1).
The return code value is set as follows:
SY-SUBRC = 0 The line was deleted.
SY_SUBRC = 4 No lines could be deleted, since no line exists with the primary key specified.
Example
Delete the booking with the booking number 3 for the Lufthansa flight 0400 on 28.02.1995 (in the current client):
TABLES SBOOK.
SBOOK-CARRID = 'LH'.
SBOOK-CONNID = '0400'.
SBOOK-FLDATE = '19950228'.
SBOOK-BOOKID = '00000003'.
DELETE SBOOK.
Addition 1
... FROM wa
Effect
Takes the primary key for the line to be deleted not from the table work area dbtab , but from the explicitly specified work area wa . Here, the key values from left to right are taken from wa according to the structure of the primary key in the table work area dbtab (see TABLES ). The structure of wa is not taken into account. Therefore, the work area wa must be at least as wide (see DATA ) as the primary key in the table work area dbtab and the alignment of the work area wa must correspond to the alignment of the primary key in the table work area. Otherwise, you get a runtime error.
Note
If a work area is not explicitly specified, the values for the line to be deleted are taken from the table work area dbtab , even if the statement appears in a subroutine (see FORM ) or Funktionsbaustein (see FUNCTION ) where the table work area is stored in a formal parameter or a local variable of the same name.
Addition 2
... CLIENT SPECIFIED
Effect
As with variant 1.
Variant 3
DELETE dbtab FROM TABLE itab.
DELETE (dbtabname) FROM TABLE itab.
Addition
... CLIENT SPECIFIED
Effect
Mass deletion: Deletes all database table lines for which the internal table itab contains values for the primary key fields. The lines of the internal table itab must satisfy the same condition as the work area wa in addition 1 to variant.
The system field SY-DBCNT contains the number of deleted lines, i.e. the number of lines of the internal table itab for whose key values there were lines in the database table dbtab .
The return code value is set as follows:
SY-SUBRC = 0 All lines from itab could be used to delete lines from dbtab .
SY_SUBRC = 4 For at least one line of the internal table in the database table, there was no line with the same primary key. All found lines are deleted..
Note
If the internal table itab is empty, SY-SUBRC and SY-DBCNT are set to 0.
Addition
... CLIENT SPECIFIED
Effect
As with variant 1.
Variant 4
DELETE dbtab VERSION vers.
DELETE *dbtab VERSION vers.
Note
This variant is obsolete, since variants 1 - 3 allow you to specify the database table name dynamically.
Effect
Deletes a line in a database table, the name of which is taken from the field vers at runtime. The database table must be known to the ABAP/4 Dictionary and its name must conform to the following naming convention: It must begin with 'T' and can consist of four additional characters. The field vers must contain the table name without a leading 'T'. Only lines in the current client are deleted. The line to be deleted is taken from the statically specified table work area dbtab or *dbtab .
The return code value is set as follows:
SY-SUBRC = 0 The line was deleted.
SY_SUBRC = 4 No lines could be deleted because no line existed with the specified primary key.
Thanks & Regards
Sreenivasulu P
05-16-2006 9:03 AM
Hi Sreenivasulu Ponnadi ,
Kindly do not copy/paste ABAP key word documentation.
Regards
Raja
05-16-2006 8:32 AM
hii
try this ..
i guess it wont consume much of system resources
<b>select * from ZTABLE.
delete ztable.
endselect.</b>
or
Define an internal table of structure as your table.
select all the records into the internal table.
delete from ztable from table internaltable.
<b>data: itab type table of ztable.
select * into table itab from ztable.
DELETE ztable FROM TABLE itab .</b>
regards
Naresh
05-16-2006 8:54 AM
Hi Raj,
delete dbtab.
will do that.
or else if you have table maintenance then select all and delete the entries.
Regards
vijay
05-16-2006 8:58 AM
Hi Raj,
Refer the following demo programs in SE38.
DEMO_INT_TABLES_DELETE_FROM
DEMO_INT_TABLES_DELETE_IND_1
DEMO_INT_TABLES_DELETE_IND_2
DEMO_INT_TABLES_DELETE_IND_3
DEMO_INT_TABLES_DELETE_WHERE
Use the one which suits your requirement.
<b>Please reward points if it helps.</b>
Regards,
Amit Mishra
05-16-2006 9:01 AM
Hi Raj,
u can try this
select * from ztable package size 10000 into table itab.
delete ztabls from table itab.
this will delete 10,000 records at a time,
04-17-2019 9:51 AM
04-17-2019 9:56 AM
This is 13 years old question.
sawa.ito is doing some updates and strangely refreshing many old questions???
04-17-2019 10:03 AM
ohh my bad, yeah I forget to check the dates, I also seen one very old thread!
Thanks for update! 🙂