Skip to Content
author's profile photo Former Member
Former Member

Is there any syntax to modify database table from internal table

Friends,

I have a problem. Is there a syntax to modify database table from internal table transporting field1 field2 .

Ex: MODIFY <dbtable> FROM TABLE <Internal Table> TRANSPORTING field1 field2.

for the performance wise other than modifying the database from wa in a loop.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 07:10 AM

    HI

    YES

    see this

    • Modify the database table as per new dunning procedure

    MODIFY fkkvkp FROM TABLE <b>lt_fkkvkp</b> .

    bold one is internal table

    <b>Reward i fusefull</b>

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 07:12 AM

    HI

    BEFORE MODIFYING THE DATBASE TABLE YOU NEED TO LOCK THAT TABLE

    AND MODIFY IT

    AND UNLOCK IT AGAIN

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 07:12 AM

    UPDATE dbtab - source

    Syntax

    ... { {SET set_expression [WHERE sql_cond]}

    | {FROM wa|{TABLE itab}} }.

    Alternatives:

    1. ... SET set_expression [WHERE sql_cond]

    2. ... FROM wa

    3. ... FROM TABLE itab

    Effect

    The specifications in source define which rows and columns are changed. Either individual columns are changed using the addition SET or entire rows are overwritten using the addition FROM.

    After FROM, either a non-table-type data object wa or an internal table itab can be specified. The content of these objects determines - on the one hand - which row(s) is/are changed, and

    - on the other hand - which values are used to overwrite the row(s).

    Alternative 1

    ... SET set_expression [WHERE sql_cond]

    Effect

    After the addition SET, the changes are specified in a list of change expressions in set_expression.

    The addition WHERE uses a logical expression sql_cond to define in which rows of the database table the changes are executed. For the logical expression sql_cond, the same applies as for the WHEREcondition of the statement SELECT, with the exception that no subqueries are to be evaluated in the database table to be changed. If no WHERE condition is specified, all the rows in the database table are changed. If a column of the type STRING or RAWSTRING is changed, the primary key must be fully specified in the WHERE condition.

    The content of primary key fields can only be changed if the respective database table is not linked with a search help and if pool and cluster tables are not accessed. If a link was to be created through the changes and this row would cause double entries in the primary key or a unique secondary index of the database table, no row is changed and sy-subrc is set to 4.

    Notes

    Before release 6.10, SET could only be specified for static specification of the database table.

    Before release 6.10, no dynamic logical expressions could be used in the WHERE condition of the statement UPDATE.

    Example

    Dynamic conversion of the content of an arbitrary column in an arbitrary database table of a previous currency in Euro.

    PARAMETERS: table TYPE c LENGTH 30,

    column TYPE c LENGTH 30,

    old_curr TYPE sycurr.

    DATA: set_expr TYPE string,

    condition TYPE string.

    CONCATENATE column ` = 'EUR'`

    INTO set_expr.

    CONCATENATE column ` = old_curr`

    INTO condition.

    TRY.

    UPDATE (table)

    SET (set_expr)

    WHERE (condition).

    CATCH cx_sy_dynamic_osql_error.

    MESSAGE `Error in update!` TYPE 'I'.

    ENDTRY.

    Alternative 2

    ... FROM wa

    Effect

    If you specify a non-table-type work area wa, the system will search for a row in the database table which, in its primary key, has the same content as the respective beginning part of the work area. The content of the work area is interpreted in its non-converted form and in accordance with the structure of the database table or the view. The content of the work area is assigned to this row. The assignment takes place without conversion from the left to the right in accordance with the structure of the database table or the view. The work area must fulfill the prerequisites for use in Open SQL statements.

    If there is no row with the same content for the primary key in the database or if the change would lead to a double entry in a unique secondary index, the line is not changed and sy-subrc is set to 4.

    Notes

    The work area wa should always be declared in relation to the database table or the view in the ABAP Dictionary.

    If you have a static specification of the database table or the view, the specification of the work area using FROM wa can be omitted outside of classes - provided a table work area dbtab for the respective database table or the view is declared using the statement TABLES. The system expands the UPDATE statement implicitly to include the addition FROM dbtab.

    Alternative 3

    ... FROM TABLE itab

    Effect

    When an internal table itab is specified, the system processes all the rows of the internal table in accordance with the rules for the work area wa. The row type of the internal table must meet the requirements for use in Open-SQL statements.

    If, in the database, there is no row with the same content of the primary key for a row in the internal table, or if the change would lead to a double entry in a unique secondary key, the respective row is not changed 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 inserted.

    Example

    Reduction of the flight cost for all of today's flights of an airline carrier in the database table SFLIGHT by the percentage percent. The calculation of the new price is always done in an internal table sflight_tab and the database table is changed accordingly.

    PARAMETERS: p_carrid TYPE sflight-carrid,

    percent TYPE p LENGTH 1 DECIMALS 0.

    DATA sflight_tab TYPE TABLE OF sflight.

    FIELD-SYMBOLS <sflight> TYPE sflight.

    SELECT *

    FROM sflight

    INTO TABLE sflight_tab

    WHERE carrid = p_carrid AND

    fldate = sy-datum.

    IF sy-subrc = 0.

    LOOP AT sflight_tab ASSIGNING <sflight>.

    <sflight>-price =

    <sflight>-price * ( 1 - percent / 100 ).

    ENDLOOP.

    ENDIF.

    UPDATE sflight FROM TABLE sflight_tab.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 07:22 AM

    The statement to Insert Records from ITAB to DBtable is

    INSERT <DBTABLE> FROM TABLE <ITAB> ACCEPTING DUPLICATE ROWS.

    The statement to Insert single Record from WA to DBtable is

    INSERT <DBTABLE> FROM <wa> .

    Check out which of these is two is correct for ur scenario.

    And also check the content of the ITAB or Wa in the debugging

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.