09-18-2007 9:30 AM
hi guys,
How do we (1) delete tables (2) delete table entries
and together deleting the other entries with corresponding foreign key?
Thanks
Message was edited by:
TY Ng
09-18-2007 9:33 AM
Hi ,
U can delete entries from the table using the following syntax.
DELETE FROM <TABLENAME>.
Variants:
1. DELETE FROM dbtab WHERE cond.
DELETE FROM (dbtabname) WHERE cond.
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 from a database table (see Relational Databases ). 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 to the ABAP Dictionary. 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 Dictionary with the maintenance status "No restriction".
DELETE belongs to the Open SQL command set.
Notes
The DELETE statement does not perform authorization checks. You must program these yourself.
The final (irrevocable) deletion of lines with the DELETE statement is not performed until after a database commit (see Logical Unit of Work (LUW)). Prior to this, you can reverse any database update with a database rollback (see Programming Transactions).
You cannot rely exclusively on the locking mechanism of the database system to synchronize several users trying to access the same dataset at the same time. You should therefore use the SAP locking mechanism.
Variant 1
DELETE FROM dbtab WHERE cond.
DELETE FROM (dbtabname) WHERE cond.
Addition:
... CLIENT SPECIFIED
Effect
Deletes lines in a database table that satisfy the WHERE clause cond. 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 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 02.28.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
See Short forms not allowed and * work areas not allowed.
Effect
These are SAP-specific short forms used to delete one 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 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 function module (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 2.
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 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.
This variant is not allowed in an ABAP Objects context. See VERSION addition not allowed.
Note
This variant is now 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 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 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.
<b>Reward points if this helps,</b>
Kiran
09-18-2007 9:33 AM
Hi ,
U can delete entries from the table using the following syntax.
DELETE FROM <TABLENAME>.
Variants:
1. DELETE FROM dbtab WHERE cond.
DELETE FROM (dbtabname) WHERE cond.
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 from a database table (see Relational Databases ). 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 to the ABAP Dictionary. 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 Dictionary with the maintenance status "No restriction".
DELETE belongs to the Open SQL command set.
Notes
The DELETE statement does not perform authorization checks. You must program these yourself.
The final (irrevocable) deletion of lines with the DELETE statement is not performed until after a database commit (see Logical Unit of Work (LUW)). Prior to this, you can reverse any database update with a database rollback (see Programming Transactions).
You cannot rely exclusively on the locking mechanism of the database system to synchronize several users trying to access the same dataset at the same time. You should therefore use the SAP locking mechanism.
Variant 1
DELETE FROM dbtab WHERE cond.
DELETE FROM (dbtabname) WHERE cond.
Addition:
... CLIENT SPECIFIED
Effect
Deletes lines in a database table that satisfy the WHERE clause cond. 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 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 02.28.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
See Short forms not allowed and * work areas not allowed.
Effect
These are SAP-specific short forms used to delete one 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 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 function module (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 2.
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 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.
This variant is not allowed in an ABAP Objects context. See VERSION addition not allowed.
Note
This variant is now 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 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 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.
<b>Reward points if this helps,</b>
Kiran
09-18-2007 9:36 AM
Hi, the syntax is supposed to be executed in abap program or in database (sql server in my case)?
I really need solution to delete the entries, cleanly
09-18-2007 9:38 AM
Hi,
You cannot give conditions along with FROM [internal table]
The other way would be to filter out the records from the internal table and then use DELETE...
DELETE itab WHERE MATNR LIKE 'KE%'.
DELETE ZTABLE FROM itab.
These statements will delete the records where the MATNR field starts with KE..'
REGARDS,
Pritha.
09-18-2007 9:41 AM
Hi,
Donot delete any standard SAP tables and also Donot Delete the table entries in Standard tables.
If you want to delete from Z tables using SE11 this is the process.
In SE11 for the Ztable under the tab Delivery/ Maintenance, select Maintenace Alllowed.
Then you can goto Data browser and select the entries you want to delete and delete using the menu option.
For deleting the table itself just press the 'Dustbin' button in Se11.
Regards,
Sesh
09-18-2007 9:45 AM
Hi,
Why am I not supposed to delete the standard table (or entries in standard table)?
If i really need to do so, how am i gonna get rid of those data?
thanks
09-18-2007 10:02 AM
Hi,
Standard tables will contain mostly MASTER DATA or Transactional data with some dependancy with other tables.
If you directly delete data from the standard tables you might end up creating inconsistent database and this can make all other programs behave strangely.
If you want to get rid of some data from the table in any module(MM OR SD etc) then there will be a transaction specific for that module which will take care of removing the data safely with out creating any inconsistency.
Data integrity of the system depends a lot on the data in the Standard tables, so donot delete them manually, let SAP provided programs do that for you instead.
Hope you got my point.
Regards,
Sesh
09-18-2007 10:13 AM
Hi Sesh,
Yes, indeed I wanted to clear those Master Data and Transaction Data. In view of this consistency and data integrity problem, that's why I needed a solution to clear those master data and transaction data cleanly. I know it's gonna be very risky, that's why i needed some ideas to work out a careful plan.
And yes, thanks for the kind advice, really appreciate it.
I could only find the FI transactions to remove those data. But in SD and MM module, couldnt really find the right transaction to clear those data. That's why I'm thinking about deleting from tables.
In case I really need to do so, anything that I need to pay extra attention to or to take care of? Thanks
09-19-2007 1:53 AM
09-18-2007 10:04 AM
write a report program to delete the same. You wont be having authzn to delete directly. So report program should work.
DELETE FROM sflight
WHERE
fldate = sy-datum AND
seatsocc = 0.