topic Re: update in database table in Application Development and Automation Discussions

update in database table

Former Member — Thu, 20 Mar 2014 12:45:21 GMT

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.

Re: update in database table

Former Member — Thu, 20 Mar 2014 12:55:56 GMT

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

Re: update in database table

Former Member — Thu, 20 Mar 2014 12:59:49 GMT

Hey Jozef,

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

Re: update in database table

ThomasZloch — Thu, 20 Mar 2014 13:03:12 GMT

"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

Re: update in database table

adrian_mejido — Thu, 20 Mar 2014 13:04:00 GMT

Hi Yash,

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

Best regards

Re: update in database table

Former Member — Thu, 20 Mar 2014 13:07:51 GMT

Thank you guys for the reply..

I got your point.

But another thing which I want to ask is:

Is the code is true?

Re: update in database table

Former Member — Thu, 20 Mar 2014 13:09:37 GMT

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.

Re: update in database table

Former Member — Thu, 20 Mar 2014 13:12:56 GMT

Hi Sachin,

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

Re: update in database table

ThomasZloch — Thu, 20 Mar 2014 13:16:21 GMT

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

Re: update in database table

ronaldo_aparecido — Thu, 20 Mar 2014 13:21:10 GMT

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.

Re: update in database table

ronaldo_aparecido — Thu, 20 Mar 2014 13:22:27 GMT

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

Re: update in database table

Former Member — Thu, 20 Mar 2014 13:23:26 GMT

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?

Re: update in database table

Former Member — Thu, 20 Mar 2014 13:31:46 GMT

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.

Re: update in database table

Former Member — Thu, 20 Mar 2014 13:37:58 GMT

Try this

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

Re: update in database table

Former Member — Thu, 20 Mar 2014 13:42:08 GMT

Sachin your code is updating the data.

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

Re: update in database table

Former Member — Thu, 20 Mar 2014 14:21:06 GMT

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.

Re: update in database table

Former Member — Thu, 20 Mar 2014 14:23:53 GMT

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

Re: update in database table

RaymondGiuseppi — Thu, 20 Mar 2014 15:02:02 GMT

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

Re: update in database table

SuhaSaha — Thu, 20 Mar 2014 15:13:16 GMT

It's a case of RTFM for the OP.

Re: update in database table

Former Member — Thu, 20 Mar 2014 16:07:28 GMT

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