Skip to Content
avatar image
Former Member

Validating Duplicate Entries In itab

Hi All

How to approach for restricting duplicate entries in the Database?

The scene is that I've to enter some data in a table control . and while saving, I've to check whether the same entry already exists in the database or not.

How to approach for this? Awaiting your valuable response.

Thanks in advance

Pradipta

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

8 Answers

  • avatar image
    Former Member
    May 23, 2006 at 09:21 AM

    Hi,

    1. Before saving you can fire a select statement to see if there a entry with the same values

    2. Get all the existing values from the DB into a internal table and check against the internal table the rows in the table control.

    Regards,

    Ravi

    Note : Please mark all the helpful answers

    Add comment
    10|10000 characters needed characters exceeded

    • just declare an itab of type your database table.

      data: itab type standard table of zmytable .

      fill the values to itab.

      now simply use

      <b>Modify</b> as suggested by Vijay

      <b>MODIFY zmytable FROM TABLE itab .</b>

      this will

      insert new entry - if there is no entry exists in DB table

      modify existing entry - if there is a record with simlar key in DB table it will modify the record

      this is like <b>INSERT</b> with <b>accepting duplicate entries .</b>

      both <b>modify</b> and <b>insert with accepting duplicate entries</b> checks for duplicate primary keys.

      Regards

      Raja

  • May 23, 2006 at 09:23 AM

    While updating, check in the database using a select single statement.

    loop at itab.

    select single *

    from ztab

    where keyfield1 = itab-field1

    and key-field2 = itab-field2.

    if sy-subrc <> 0.

    insert ztab from itab.

    endif.

    endloop.

    • where itab is the internal table that has the records in the table control.

    or simply use modify statement

    modify ztab from wa. (Modifies the record if already exists or creates an entry if no entry exists).

    Regards,

    Ravi

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 23, 2006 at 09:24 AM

    Hi pradipta,

    1. We have to check with SELECT statement only.

    2. like this :

    Just copy paste in new program.

    3.

    report abc.

    *----


    data : itab like t001.

    select single * from t001

    into itab

    where bukrs = '1333'.

    *----


    if sy-subrc = 0.

    write :/ 'record exits'.

    else.

    write :/ 'record does notexits'.

    endif.

    regards,

    amit m.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 23, 2006 at 09:27 AM

    Hi Pradipta

    for this u have to carry out individual checks for each field before updating the database

    for eg

    check not <db fname> = itab-fname and ....

    modify database.

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 23, 2006 at 09:28 AM

    Hi,

    I think you can check for the primary field in that particular record using the SELECT statement.if the record already exist, then delete the duplicate entries. or you can use

    DELETE ADJACENT DUPLICATES FROM ITAB COMPARING <primary field>.

    This will delete all the duplicate entries based on that field.

    Regards,

    Aswin

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 23, 2006 at 09:32 AM

    Hai Mishra

    DELETE ADJACENT DUPLICATES FROM itab.

    Additions

    1. ... COMPARING f1 f2 ...

    2. ... COMPARING ALL FIELDS

    Effect

    Deletes neighboring, duplicate entries from the internal table itab . If there are n duplicate entries, the first entry is retained and the other n - 1 entries are deleted.

    Two lines are considered to be duplicated if their default keys match.

    The return code value is set as follows:

    SY-SUBRC = 0 At least one duplicate exists, at least one entry deleted.

    SY_SUBRC = 4 No duplicates exist, no entry deleted.

    Addition 1

    ... COMPARING f1 f2 ...

    Effect

    Two lines of the internal table itab are considered to be duplicates if the specified fields f1 , f2 , .... match.

    Addition 2

    ... COMPARING ALL FIELDS

    Effect

    Two lines are considered to be duplicates if all fields of the table entries match.

    Notes

    The DELETE ADJACENT DUPLICATES statement is especially useful if the internal table itab is sorted by fields (whether in ascending or descending order) which were compared during duplicate determination. In this case, the deletion of neighbouring duplicates is the same as the deletion of all duplicates.

    If a comparison criterion is only known at runtime, it can be specified dynamically as the content of a field name by using COMPARING ... (name) ... . If name is blank at runtime, the comparison criterion is ignored. If name contains an invalid component name, a runtime error occurs.

    Comparison criteria - statistically or dynamically specified - can be further restriced by specifying the offset and/or length.

    Note

    Performance

    Deleting a line from an internal table incurs index maintenance costs which depend on the index of the line to be deleted. The runtime depends on the line width of the table.

    For example, deleting a line in the middle of an internal table with 200 entries requires about 10 msn (standardized microseconds).

    Deleting a range of entries with " DELETE itab FROM idx1 TO idx2. " deleting a set of entries with " DELETE itab WHERE ... " only incur index maintenance costs once. Compared with a LOOP , which deletes line-by-line, this is much faster.

    To delete neighboring, duplicate entries from an internal table, use the variant " DELETE ADJACENT DUPLICATES FROM itab. " instead of LOOP constructions.

    Thanks & regards

    Sreeni

    Add comment
    10|10000 characters needed characters exceeded

  • May 23, 2006 at 09:33 AM

    Hi Pradipta,

    Select within loop will not be a good option. It will be a performance issue.

    Use the following code instead to check for the duplicate entries.

    If not itab_table_data[] is initial.
    	select field1 field2
            from ztab
            into table lit_itab
            for all entries in itab_table_data
            where field1 = itab_table_data-field1.
    Endif.
    
    sort lit_itab by field1 field2.
    
    Loop at itab_table_data into wa_table_data.
    	Read table lit_itab into wa_itab
            with key field1 = wa_table_data-field1
                     field2 = wa_table_data-field2
            Binary search.
    	if sy-subrc eq 0.
    *		Show error message or do whatever you want 
            Endif.
    Endloop.

    <b>Reward points if it helps.</b>

    Regards,

    Amit Mishra

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 23, 2006 at 09:36 AM

    hi for performance issues instead of looping while entering the data, sort the table according to the primary keys and delete duplicate entries

    delete adjacent duplicates comparing primarykey

    Add comment
    10|10000 characters needed characters exceeded