topic Re: delete in Application Development and Automation Discussions

delete

Former Member — Mon, 09 Oct 2006 05:38:36 GMT

Hi All,

How to delete a field value from a data base table.

If my condition is satisified.i need to delete the from value form the table.

Plese give me the syntax in all posible ways.

Thanks in Advance.

Thanks&Regrds.

Ramu.

Re: delete

Former Member — Mon, 09 Oct 2006 05:41:50 GMT

The Open SQL statement for deleting lines from a database table is:

DELETE [FROM] target lines.

It allows you to delete one or more lines from the database table target. As described in the section Inserting Table Lines, the database table target can be specified statically and dynamically.

To select the lines that you want to delete using a condition, use the following:

DELETE FROM target WHERE cond.

All of the lines in the database table that satisfy the conditions in the WHERE clause are deleted. The FROM expression must occur between the keyword and the database table.

Have a look at below link.

http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb3aef358411d1829f0000e829fbfe/content.htm

Best Regards,

Vibha Deshmukh

*Plz mark useful answers

Re: delete

jayanthi_jayaraman — Mon, 09 Oct 2006 05:42:38 GMT

Hi,

if condition.

delete ztab where field1 = value.

endif.

Message was edited by: Jayanthi Jayaraman

Re: delete

Former Member — Mon, 09 Oct 2006 05:45:47 GMT

hi,

check this from help..

<b>DELETE { {FROM target [WHERE sql_cond]}

| {target FROM source} }.</b>

Effect

The statement DELETE deletes one or more rows from the database table specified in target. The rows that are to be deleted are specified either in a WHERE condition sql_cond or with data objects in source.

System Fields

The statement DELETE sets the values of the system fields sy-subrc and sy-dbcnt.

sy-subrc Meaning

0 A least one row was deleted.

4 At least one row could not be deleted, since it was not found in the database table.

The statement MODIFY sets sy-dbcnt to the number of deleted rows.

<b>DELETE dbtab - cond</b>

Syntax

... WHERE sql_cond.

Effect

The WHERE addition uses a logical expression sql_cond to specify which rows in the database table are deleted. The same applies to the logical expression sql_cond as for the WHERE condition of the SELECT statement. If there is no row in the database that satisfies the WHERE condition, no row is deleted and sy-subrc is set to 4. If no WHERE condition is specified, all rows are deleted.

Note

As of Release 6.10, specifying the WHERE condition is optional. Prior to Release 6.10, you had to specify the WHERE condition in this variant of the DELETE statement and you could not use dynamic logical expressions.

Example

In the following example, all an airline's flights for today in which no seats are occupied are deleted from the database table SFLIGHT (see also, example to dtab-source).

PARAMETERS p_carrid TYPE sflight-carrid.

DELETE FROM sflight

WHERE carrid = p_carrid AND

fldate = sy-datum AND

seatsocc = 0.

Syntax

... FROM wa|{TABLE itab}.

Alternatives:

1. ... FROM wa

2. ... FROM TABLE itab

Effect

After FROM, you can specifiy a data object wa that is not table-type or an internal table itab. The content of the data objects determines the row(s) to be deleted.

Alternative 1

... FROM wa

Effect

If you specify a work area wa that is not table-type, a row is searched for in the database table whose primary key content is the same as that of the corresponding initial part of the work area. The content of the work area is not converted and is interpreted according to the structure of the database table or view. This row is deleted. The work area must meet the prerequisites for use in Open SQL statements.

If there is no row in the database with the same content as the primary key, no row is deleted and sy-subrc is set to 4.

Notes

The work area wa should be declared wit reference to the database table or view in the ABAP Dictionary using the length of the primary key.

If the database table of view are specified statically, you do not have to specify the work area with FROM wa outside of classes if a table work area dbtab is declared for the relevant database table or view using the TABLES statement. The system then implicitly adds the FROM dbtab addition to the DELETE statement.

Alternative 2

... FROM TABLE itab

Effect

If an internal table itab is specified, the system processed all rows of the internal table according to the rules for the work area wa. The row type of the internal table must meet the prerequisites for use in Open SQLstatements.

If, for a row of the internal table, there is no row in the database with the same content as the primary key, the corresponding row is ignored and sy-subrc is set to 4. If the internal table is empty, sy-subrc is set to 0. The system field sy-dbcnt is always set to the number of rows actually deleted.

Example

In the following example, all today's flights of an airline in which no seats are occupied are deleted from the database table SFLIGHT. The client field must be in the row structure of the internal table sflight_key_tab. Otherwise it does not cover the primary key of the database table and incorrect key values will be accepted as a result. This example has the same function as that for dtab-cond, but it requires two database accesses. The deleted rows are recorded in the internal table.

PARAMETERS p_carrid TYPE sflight-carrid.

TYPES: BEGIN OF sflight_key,

mandt TYPE sflight-mandt,

carrid TYPE sflight-carrid,

connid TYPE sflight-connid,

fldate TYPE sflight-fldate,

END OF sflight_key.

DATA sflight_key_tab TYPE TABLE OF sflight_key.

SELECT carrid connid fldate

FROM sflight

INTO CORRESPONDING FIELDS OF TABLE sflight_key_tab

WHERE carrid = p_carrid AND

fldate = sy-datum AND

seatsocc = 0.

DELETE sflight FROM TABLE sflight_key_tab.

do reward if it helps,

priya.

Re: delete

Former Member — Mon, 09 Oct 2006 05:50:22 GMT

Hi Ramu,

lets say you are checking the condition with a simple IF statment, then

IF <Condition> = <TRUE>.

delete TABLE where field = value.

ENDIF.

Here 1 single record will be deleted fromthe standard table(TABLE).

Regards

SUdheer

Re: delete

Former Member — Mon, 09 Oct 2006 05:52:12 GMT

Hi Ramu,

You can delete the value from database table by using the following syntax:

IF <condition satisfied>.

delete <dbtabname> from <workarea>.

"Workarea will be of the same type as the table & will contain the values of table line to be deleted"

ENDIF.

or u can use this syntax.

delete from <dbtabname> where <condition>.

In the condition part above,you can mention the condition for which u want to delete the table entries.

or use the syntax:

delete <dbtabname> from <itab>.

Here itab is the internal table which contains all the values which you want to delete from the database table dbtabname.

Hope this information helps.Please reward points if you find the info. useful.

Regards,

Chetan.

Re: delete

andreas_mann3 — Mon, 09 Oct 2006 06:00:04 GMT

hi,

i think you wanna clear the field:

update ztab

set field1 = space "( numbers 0 )

where field 2 > xyz

pls reward useful answers

Message was edited by: Andreas Mann

Re: delete

Former Member — Mon, 09 Oct 2006 06:12:57 GMT

Hi ramu,

1. How to delete a field value from a data base table

I suppose, u want to CLEAR the

value of a particular field, in database table,

for a particular record.

2. Then do like this.

3. a) First select the particular record,

in some work area eg. WA.

b) CLEAR the field in WA.

if condition.

CLEAR WA-FIELD1.

ENDIF.

C) then just use

MODIFY ZDBTABLE FROM WA.

regards,

amit m.