topic Re: how to modify the database table using a index in Application Development and Automation Discussions

how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 03:17:50 GMT

i have captured a index for the modifeid recoed in a varialble now it want to modify the database table using this index

for eg ztabel

ho to do it pls tell me the syntex

regards

Nishant

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 03:29:10 GMT

Hi Nishant,

I seen your lots of post on table modification.

The best way is just loop at internal table and get the data in work area and use that work area to modify the table.

Regards,

Atish

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 03:43:38 GMT

hi atish

i have alredy tried this and its not working and i want to modify using index

pls suggest?

i have a database table zmm_aims_opn

with fields

customer number name email

all are primary keys?

i have captured the data in t_modify internal table which contains the data whihc is to be modified

but it is beig inserted not modified as this int table has the modified data hence it is not able to match with the database table and hence it is inserting?

now ther are tow options to use cetain condtion to comapre the record in the d atabase table or use index

index will be better one pls suggest

or let me know if i can use where conditon

the modification code is as below

loop at t_aims_opn_modify.

modify zmm_aims_opn from table <b>t_aims_opn_modify </b> ( this is internal table having all records to be modified)

message s001 with 'Data saved'(004).

refresh:t_aims_opn_modify.

endloop.

lse.

message s001 with 'Data already saved'(003).

regards

nishant

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 03:46:38 GMT

Hi Nishant,

As I understand you want to modify the data of the key field. Correct me if I am wrong.

If yes then the MODIFY will not solve your purpose.

You need to use UPDATE...SET in this case.

Regards,

Atish

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 03:50:06 GMT

yes i want to modify the data of email

and all three are primary keys?

Please suggest?

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 04:01:09 GMT

hi Atish

please suggest how to use update set in above case

as the above inernal table contains data to be modified

also one more internal tabel i have zcus w hich has all the new records it is having the record modified with new one and the database table should have the recods as in this zcust internal table

let me know how to use update ..set in above my code ?

regards

Nishant

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 04:09:39 GMT

Hi Nishant ,

I feel it not possible to modify a field in a table which is part of the primary key ,so you even if you perofrm a update operation it will not be reflected in the database,

Or you will have to use the addition SET with the command UPDATE

Regards

Arun

Message was edited by:

Arun R

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 04:10:37 GMT

Hi Nishant,

loop at itab into wa_itab.

UPDATE ztable SET name = wa_itab-name

email = wa_itab-email.

ENDLOOP>

Just check the syntaxes once and write the code your problem wil be solved.

Regards,

Atish

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 04:28:02 GMT

hi atish

the code before the earlier give code is as below

here after this code the internal table T_cust_em contaains the modifed data

and it contains the record that has modified

or eg the 8th record is modified....so it contains the 8th record

here we can use index to this internal table to read the record which has been updated WHICH IS 8TH RECORED FOR EG

and then delte it from database but how to code FOR IT HERE and how to delte from database this specific record

can i use like delter from database table (zcustemail ) where

ship_to = zcust_em-ship_to

and ship_to_name = zcust_ema-ship-to_name

and emai_id = zcust_em-emai_id.

let me know if this is possible delteting the record will be a better option

like i catch the index which was modifed and use that index to read thi internal table to fetch the record whch needs to be delted from database table and use where conditon to delete from database table? pls suggest

PLS SUGGEST? ITS URGENT?

IF U WNAT I CAN PAST THE WHOLE CODE?

my problme is how to delte from databASE? THE recoed which has been modified?

modify t_zcust_em index table_zcust_em-current_line.

select single *

from zcustemail

where ship_to eq t_zcust_em-ship_to

and email_id eq t_zcust_em-email_id.

and ship_to_name = t_zcust_em-ship_to_name.

if sy-subrc ne 0.

move:t_zcust_em to t_zcust_em_modif_tmp.

append t_zcust_em_modif_tmp.

clear:t_zcust_em_modif_tmp.

wa_index = table_zcust_em-top_line.

endif.

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 04:39:35 GMT

Hello Nishant,

I am getting confused with your requirement. Do you want to delete the record from DB table or you want to MODIFY the record from table which has all the primary keys?

Regards,

Atish

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 05:51:02 GMT

hi Atish

i want to modify but in my case the modified recored is added to the table rather then modified

but as moficcatio is not possible so i want to delter the record earleir one which was modified so automatically it will show the new record in the database as modfied as the earlier one is deleted

regards

Nishant

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 06:19:34 GMT

Hi Nishant,

by using UPDATE you will be able to modify the record so no need to worry about all other logic or anything else.

Just use UPDATE as I mentioned earlier.

Can you close the other threads and keep only one open if problem not solved.

Regards,

Atish

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 06:32:42 GMT

hi Atish

i wnat to know the code for update to modify?

for the email id

pls let me kow on that

regards

Nishant

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 06:36:20 GMT

Hi Nishant,

I already given you the code in my above post. You just need to finetune it.

Regards,

Atish

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 06:38:57 GMT

Hi Atish

the code u ahve posted?

can u please let me k now on that since ship_to is also a prirmay key and ship_to_name is also a primary key

and email_id also a primary key as i told earleir and my internal table contains the data which needs to be modified

so h0ow will it match the data in the ztable as the ztable will certainly not ahve the record which this internal table has as this is modified only?

loop at itab into wa_itab.

UPDATE ztable SET name = wa_itab-name

email = wa_itab-email.

ENDLOOP>

Just check the syntaxes once and write the code your problem wil be solved.

Regards,

Atish

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 06:44:02 GMT

Hi Nishant,

You need to specify WHERE clause and in that you can provide other 2 key fields value

UPDATE <ztable> SET email = wa-email

WHERE

ship_to = wa-ship_to

ship_to_name = wa-ship_to_name.

Hope you got the idea.

Regards,

Atish

Re: how to modify the database table using a index

Former Member — Fri, 05 Oct 2007 09:14:11 GMT

Hi Atish

i got the idea but my problme is that this will not work as the modification internal table contains the data which is modified so this comparision will mismatch and database recored will not be updated...

please seee my code as belwo whole

PROCESS AFTER INPUT.

module get_t_zcust_em_for_deletion.

loop with control table_zcust_em.

MODULE USER_COMMAND_0300.

endloop.

module handling_function_0300.

MODULE USER_COMMAND_0300 INPUT.

w_crnt_line = table_zcust_em-top_line.

w_lines = sy-loopc.

modify t_zcust_em index table_zcust_em-current_line.

select single *

from zcustemail

where ship_to eq t_zcust_em-ship_to

and email_id eq t_zcust_em-email_id.

if sy-subrc ne 0.

move:t_zcust_em to t_zcust_em_modif_tmp.

append t_zcust_em_modif_tmp.

clear:t_zcust_em_modif_tmp.

wa_index = table_zcust_em-top_line.

endif.

ENDMODULE. " USER_COMMAND_0300 INPUT

perform fill_modif_value.

perform modify_data.

perform refresh_data1.

ORM fill_modif_value.

loop at t_zcust_em_modif_tmp.

move:

t_zcust_em_modif_tmp-ship_to to t_zcust_em_modify-ship_to ,

t_zcust_em_modif_tmp-SHIP_TO_NAME to t_zcust_em_modify-SHIP_TO_NAME ,

t_zcust_em_modif_tmp-email_id to t_zcust_em_modify-email_id.

sy-uzeit to t_zcust_em_modify-EXTRTIME

sy-uname to t_zcust_em_modify-USER_ID

append t_zcust_em_modify.

clear:t_zcust_em_modify.

endloop.

ENDFORM. " fill_modif_value

if not t_zcust_em_modify[] is initial.

loop at t_zcust_em_modify.

modify zcustemail from table t_zcust_em_modify.

UPDATE zcustemail SET
ship_to_name = t_zcust_em_modify-ship_to_name
ship_to = t_zcust_em_modify-ship_to

email_id = t_zcust_em_modify-email_id.

message s001 with 'Data saved'(004).

refresh:t_zcust_em_modify.

*clear:t_zcust_em_modif_tmp.

endloop.

else.

message s001 with 'Data already saved'(003).

endif.

refresh:t_zcust_em_modif_tmp.

**nv modify problem

clear:t_zcust_em_modif_tmp.

**nv modify problem

ENDFORM. " modify_data

FORM refresh_data1.

select * from zcustemail into table t_zcust_em.

ENDFORM. " refresh_data1

this is my above code Please suggest where to use and how to use update

regards

arora