topic Re: DELETE FROM ZTABLE in Application Development and Automation Discussions

DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 09:36:47 GMT

SELECT * FROM ZTABLE INTO TABLE it_table1.

DELETE ZTABLE FROM TABLE it_table1.

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 09:37:56 GMT

SORRY

imade this and it's not deleteing the table

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 09:38:48 GMT

Whats the query?

Re: DELETE FROM ZTABLE

Laxmana_Appana_ — Tue, 04 Jul 2006 09:39:50 GMT

Hi,

Check this syntax :

Variants:

1. DELETE FROM dbtab. or

DELETE FROM (dbtabname).

2. DELETE dbtab FROM wa. or

DELETE (dbtabname) FROM wa.

3. DELETE dbtab FROM TABLE itab. or

DELETE (dbtabname) FROM TABLE itab.

4. DELETE dbtab. or

DELETE *dbtab.

5. DELETE dbtab VERSION vers. or

DELETE *dbtab VERSION vers.

Effect

Deletes a set of lines in a database table (see relational database). You can specify the name of the database table either directly in the program in the form DELETE FROM dbtab ... or at runtime as the contents of the variable dbtabname in the form DELETE FROM (dbtabname) .... In both cases, the database table must be known to the ABAP Dictionary. By default, the system deletes only data from the current client. You can only use a view to delete data, if the view refers to a single table and was created in the ABAP Dictionary with the maintenance status "no restriction".

DELETE is part of the OPEN SQL command set.

In some cases, the syntax rules that apply to Unicode programs are different than those for non-Unicode programs.See Open SQL and Unicode.

Notes

Authorization checks are not supported by the DELETE statement and must be carried out at program level.

When you delete lines using the DELETE command, the process is only complete after a database commit (see LUW). Before the commit, any database change can be reset using a database rollback (see Programming Transactions).

In dialog systems, synchronizing parallel user access to a single dataset cannot be handled by the locking mechanism of the database system alone. In many cases, you therefore have to use the SAP locking mechanism.

Variant 1

DELETE FROM dbtab. or

DELETE FROM (dbtabname).

Extras:

1. ... WHERE condition

2. ... CLIENT SPECIFIED

3. ... CONNECTION con

Effect

Deletes lines in a database table. If you do not specify a WHERE clause, the system deletes all lines (in the current client). If you specify a WHERE clause, the system deletes all lines that fulfill the WHERE condition.

After the statement is executed, the system field SY-DBCNT contains the number of lines that were deleted.

The Return Code is set as follows:

SY-SUBRC = 0:

At least one line was deleted.

SY-SUBRC = 4:

No line was deleted since no lines were selected.

Example

Delete all flight bookings (in the current client):

DELETE FROM SBOOK.

Addition 1

... WHERE condition

Effect

The system deletes only those lines that fullfill the

condition specified in the WHERE Clause.

Example

Delete all bookings of Lufthansa flight 0400 on 02/28/2001 (in the current client):

DELETE FROM SBOOK WHERE CARRID = 'LH' AND

CONNID = '0400' AND

FLDATE = '20010228'.

Addition 2

... CLIENT SPECIFIED

Effect

Switches off automatic client handling. This allows you

to delete data across all clients in case of client-specific tables. The client field is then handled like a normal table field for which you can specify appropriate conditions in the WHERE clause.

The addition CLIENT SPECIFIED must be specified directly after the name of the database table.

Addition 3

... CONNECTION con

Effect

The Open SQL command is not executed on the

standard database but on the Secondary Database Connection specified using con. con is the name of the database connection as it was specified in the table DBCON in the column CON_NAME. The database connection con can als be specified dynamically in the form (source_text) - the field source_text contains the name of the database connection and must be type C or STRING.

The CONNECTION con addition must be specified directly after the name of the database table or after the CLIENT SPECIFIED addition.

Variant 2

DELETE dbtab FROM wa. or

DELETE (dbtabname) FROM wa.

Extras:

1. ... CLIENT SPECIFIED

2. ... CONNECTION con

Effect

These are SAP-specific short forms to delete a single line from a database table. The primary key for the line to be deleted is taken from the explicitly specified work area wa. The key values are read from left to right according to the structure of the primary key of the database table. The structure of wa is not considered. This is why the work area wa must be at least as wide (DATA) as the primary key of the database table dbtab, and the alignment of the work area wa must correspond to that of the database table. Otherwise, a runtime error occurs.

If the database table dbtab or the work area wa contain strings, wa must be compatible with the line structure of dbtab.

After the statement is executed, the sytem field SY-DBCNT contains the number of lines that were deleted (0 or 1).

The Return Code is set as follows:

SY-SUBRC = 0:

The line was deleted.

SY-SUBRC = 4:

No line could be deleted since there was no line with the primary key specified.

Example

Delete the booking with the booking number 3 of Lufthansa flight 0400 on 02/28/2001 (in the current client):

DATA wa TYPE sbook.

wa-carrid = 'LH'.

wa-connid = '0400'.

wa-fldate = '20010228'.

wa-bookid = '00000003'.

DELETE sbook FROM wa.

Addition 1

... CLIENT SPECIFIED

Effect

As in variant 1.

Addition 2

... CONNECTION con

Effect

As in variant 1.

Variant 3

DELETE dbtab FROM TABLE itab.

DELETE (dbtabname) FROM TABLE itab.

Extras:

1. ... CLIENT SPECIFIED

2. ... CONNECTION con

Effect

Deletes sets: All lines of the database table for which the internal table itab contains values for the fields of the primary key are deleted. The lines of the internal table itab must fulfill the same conditions as the work area wa in variant 2.

The system field SY-DBCNT contains the number of lines that were deleted, that is, the number of lines in the internal table itab for whose key values lines existed in the database table dbtab.

The Return Code is set as follows:

SY-SUBRC = 0:

All lines in itab could be used for deleting lines in dbtab.

SY-SUBRC = 4:

For at least one line of the internal table, the database table did not contain a line with the same primary key. All lines found were deleted.

Note

If the internal table itab is empty, SY-SUBRC and SY-DBCNT are set to 0.

Addition 1

... CLIENT SPECIFIED

Effect

As in variant 1.

Addition 2

... CONNECTION con

Effect

As in variant 1.

Variant 4

DELETE dbtab. or

DELETE *dbtab.

Addition:

... CLIENT SPECIFIED

The syntax check performed in an ABAP Objects context is stricter than in other ABAP areas.See Cannot Use Short Forms and Cannot Use * Work Areas.

Note

This variant is obsolete.

Effect

These are the SAP-specific short forms of variant 2. They work like variant 2 but the work area is not specified explicitly. Instead, the table work area dbtab or *dbtab is implicitly used which is declared with a TABLES statement.

DELETE dbtab. or

DELETE *dbtab.

is also equivalent to

DELETE dbtab FROM dbtab. or

DELETE dbtab FROM *dbtab.

After the statement is executed, the system field SY-DBCNT contains the number of lines that were deleted (0 or 1).

The Return Code is set as follows:

SY-SUBRC = 0:

The line was deleted.

SY-SUBRC = 4:

No line could be deleted since no line with the primary key specified existed.

Example

Delete the booking with the booking number 3 of Lufthansa flight 0400 on 02/28/2001 (in the current client):

TABLES sbook.

sbook-carrid = 'LH'.

sbook-connid = '0400'.

sbook-fldate = '20010228'.

sbook-bookid = '00000003'.

DELETE sbook.

Note

If you do not explicitly specify a work area, the values for the line to be deleted are taken from the table work area dbtab even if the statement occurs in a subroutine (FORM) or function module (FUNCTION), in which the table work area is obscured by an identically named formal parameter or local variable.

Addition

... CLIENT SPECIFIED

Effect

As in variant 1.

Variant 5

DELETE dbtab VERSION vers.

DELETE *dbtab VERSION vers.

This variant is not allowed in an ABAP Objects context.See Cannot use the VERSION Addition.

Note

This variant has become obsolete since the variants 1 - 3 allow you to specify the database table name dynamically.

Effect

Deletes a line in a database table whose name is taken from the field vers at runtime. The database table must be known to the ABAP Dictionary, and its name must comply with the following naming convention: The name must begin with a 'T' and may contain at most five characters, including the leading 'T'. The field vers must contain the table name without the leading 'T'. The system deletes only lines in the current client. 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 line could be deleted since no line with the primary key specified existed.

Additional help

Deleting Table Lines

Laxman

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 09:40:01 GMT

Use COMMITWORK AND WAIT

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 09:40:50 GMT

Where's the problem?

You can also write:

DELETE FROM ZTABLE.

So you don't need to upload the data in an internal table.

But If I believe this statament can be used without WHERE condition only from release 4.7

Max

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 09:42:32 GMT

DELETE ZTABLE.

COMMIT WORK.

This should work.

Regards,

Ravi

Message was edited by: Ravikumar Allampallam

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 09:42:50 GMT

1) I believe, <b>it_table1</b> should have the primary key fields of the table. Can you try by adding those to internal table.

rgds,

TM.

Message was edited by: Thomas Mann

Re: DELETE FROM ZTABLE

athavanraja — Tue, 04 Jul 2006 09:46:56 GMT

SELECT * FROM ZTABLE INTO TABLE it_table1.

DELETE ZTABLE FROM TABLE it_table1.

the above should work , provided the type of itab it_table1 is same as ZTABLE.

Regards

Raja

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 09:47:17 GMT

ravi

i get the statment into is missing

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 09:57:20 GMT

DATA: IT_TABLE1 LIKE TABLE OF ZTABLE.

SELECT * FROM ZTABLE INTO TABLE it_table1.

IF SY-SUBRC = 0.

DELETE ZTABLE FROM TABLE it_table1.

IF SY-SUBRC = 0.

WRITE: 'Delete data from ZTABLE'.

COMMIT WORK AND WAIT.

ENDIF.

ELSE.

WRITE 'No data in ZTABLE'.

ENDIF.

Max

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 10:31:20 GMT

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.

Regads,

Ramesh.

Re: DELETE FROM ZTABLE

jwalithtatikonda2 — Tue, 04 Jul 2006 11:00:01 GMT

Hi liat, I am not getting you exactly what u want ? Do u want to delete the table from the database or u wanna delete all rows in the table?

Re: DELETE FROM ZTABLE

jwalithtatikonda2 — Tue, 04 Jul 2006 11:04:12 GMT

HI liat,

If u want to delete u'r Z table from DB goto se14, enter u'r db table name n select "table" radio button and press enter, then u'll find a button below as "Delete Database Table".

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 11:32:24 GMT

Hi Liat,

Consider this code. Check for SY-SUBRC after

<b>COMMIT WORK AND WAIT</b> .


DATA: it_table1 TYPE STANDARD TABLE OF ztable.

SELECT * FROM ztable INTO TABLE it_table1.

IF sy-subrc EQ 0.

  DELETE ztable FROM TABLE it_table1.

  IF sy-subrc EQ 0.

    COMMIT WORK AND WAIT.
    
    IF sy-subrc EQ 0.
      MESSAGE 'Database table deletion successful' TYPE 'S'.
    ENDIF.
    
  ENDIF.

ELSE.

  MESSAGE 'No records exists' TYPE 'E'.
  
ENDIF.

Regards,

Arun Sambargi.

Message was edited by: Arun Sambargi

Re: DELETE FROM ZTABLE

Former Member — Tue, 04 Jul 2006 11:39:11 GMT

Hi,

DELETE ZTABLE FROM TABLE it_table1.

COMMIT WORK.

Regs,

Venkat Ramanan N