update in database table

[Former Member]

Hi all,

I have an issue regarding the update query.

I am trying to insert data in a Z table.

The table consists of 4 Primary key.

In which I am trying to update one of them.

LOOP AT it_reg.

*    it_final1-schno       = v_schnumber.

       it_final1-regio         = it_reg-regio.

     it_final1-kostl       = it_reg-kostl.

    APPEND it_final1.

   ENDLOOP.

DATA : wa_rebhed_final TYPE /vcrebate/rebhed.

   MOVE-CORRESPONDING it_final1 TO wa_rebhed_final.

*  UPDATE  /vcrebate/rebhed FROM wa_rebhed_final  .

*  UPDATE /vcrebate/rebhed  SET schno = it_scheme-schno WHERE schno = it_scheme-schno..

   UPDATE /vcrebate/rebhed SET schno = it_final1-schno.

this is my code.

But I am unable to update the table.. I also tried using Modify but the modify statement is adding a row with updated field.

[Former Member]

you said you want to update a primary key. Well, you can't. Primary key fields cannot be changed in classical databases. You need to delete the existing one and insert the new one

[Former Member]

Hey Jozef,

Actually the field which I want to update is not there in db. I mean the field is empty.

[ThomasZloch]

"Empty" is also a value, depending on format, e.g. spaces for character fields. You still need to delete and re-insert with changed values, as Jozef suggested.

Thomas

[Former Member]

You can use

LOOP AT it_final1 INTO wa_rebhed_final.

UPDATE TABLE /vcrebate/rebhed SET schno = 'any value' WHERE "Put your primary key fields = wa-prim_key_fields.

ENDLOOP.

[Former Member]

Hi Sachin,

I tried your logic but it is not updating the data.

[ThomasZloch]

That's because he did not understand that you want to update a field contained in the primary key.

Why don't you try the other proposal, and what do you mean with "is the code is true" ?

Thomas

[Former Member]

Hey Thomas,

Issue is solved. I was wrong that I was trying to update the primary field.

Okay then can I modify the primary key or not?

[Former Member]

Think of the primary key as something, that is (or could be) referenced in other tables. Changing the entry would ruin everything connected to that entry. A simple delete would do the same horrible thing unless it is used in a cascade style (deleting the derived data tables first).

But why would you ever need to change the primary key? In most cases it is somehow generated, in composite primary keys there is even more encouraging - as the primary key you would like to change to can exist and your update will still fail.

So throw away any exceptions where SAP would enable you to do such stuff in OPEN SQL and look at the database as a technology. It will not allow you to change the primary key ever in other environments, so do not find ways how to trick it in SAP, as it could kick you to your face later. Use delete and insert instead.

[SuhaSaha]

These are basic DBMS concepts which should not be messed with

[adrian_mejido]

Hi Yash,

You can't modify any data of key fields, instead of that, you have to create a new record.

Best regards

[Former Member]

Thank you guys for the reply..

I got your point.

But another thing which I want to ask is:

Is the code is true?

[ronaldo_aparecido]

I think you code is wrong because.

1-If you will  update data from table then you need  use the clausure 'where' for indicate the line that will be updated , your command update all data in table.

2-You must use the update with where and in where pass all keys of table then with complete key it will work.

[ronaldo_aparecido]

If you will update data from key table you have delete record and after update.

[Former Member]

Ronaldo,

I tried your suggestion but system is giving me an error.

UPDATE /vcrebate/rebhed  FROM it_final1 .. """"" Original Code

UPDATE /vcrebate/rebhed  FROM it_final1 where schno = it_final1-schno """""" Another code.

I tried the Another code but when I press cntrl+space system doent not give me the suggestions even.

[Former Member]

Try this

UPDATE /vcrebate/rebhed SET schno = 'ABC' where schno = it_final1-schno """""" Another code.

[Former Member]

Sachin your code is updating the data.

If I try "UPDATE /vcrebate/rebhed  FROM it_final1" this updates the data.

[Former Member]

It is a myth that we can't change the primary key of a table using update statement.

We can't change, only if there is checktable or matchcode associated with the primary key field.  For reference check the link below:

UPDATE

Few years back, an ABAP beginner while practising database updates using update statement, showed me it can be done. I only believed him once he showed me a live example. It works only for some cases, not for all.

Suppose our table structure looks like this and we want to update the seqno field.

a)  Docno   Document number ( primary key )

b)  Seqno   Sequence number ( primary key ) . ( no check table )

c) any other non primary field

Step 1: Create an entry by filling only the document number (1st field ) and the non primary key.

Step 2: Now try to update the seqno field using

       update Ztable set seqno = '0001'

                       where docno = '1010010001'.

          if sy-subrc = 0.

        commit work.

          endif.

[RaymondGiuseppi]

For a more recent reference, as 45b and synchronous matchcode are getting old  : UPDATE SET set_expression [WHERE sql_cond]

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

AFAIK this is the only correct syntax of update statement allowed to change primary keys as other options use the primary key to identify the record to update, so usually in a LOOP/ENDLOOP and not FROM TABLE itab option is valid.

Regards,

Raymond

[SuhaSaha]

It's a case of RTFM for the OP.

[Former Member]

Hi ,

                                If you need to update the primary key alone mean it is not possible but indirectly you can do this using field symbol. 

1. Move the required row to internal table from data dictionary by specifying where condition with primary key value.

Eg.   Select * from zdemo_table into table it_demo.

2. delete the row by specifying primary key .

3. Now assign field symbol for your internal table.

Eg.    

FIELD-SYMBOLS <fs_itab> LIKE LINE OF it_demo.

LOOP AT it_demo ASSIGNING <fs_itab>.

   <fs_itab>-oldprimary key = 'new value for primary key'.

     "

     "

........

ENDLOOP.

4. now use insert statement by specifying <fs_itab> instead of internal tab.

Let me know if you find any inconsistencies

[Former Member]

And how will your field-symbol and insert update the existing rows? Isn't insert only inserting? It was some time ago

[Former Member]

Thanks man for the help...

[Former Member]

Thank you so much guys for the inputs..

These are very valuable and are very useful for an abaper like me.

I didnt use that SET. Simply update from table worked for me.

Is anything there which I should take care of then please share..

Thank you very much....