problem in update statment when using where condtion

[Former Member]

Hi ,

Iam using update statment to update field in Z-Table .My statment is not working fine as per my requirement.The statment is as below,

UPDATE ZFINAL_DATA SET ZDATE_FLAG = SPACE where ZGLOBAL_CODE ( SELECT LIFNR FROM LFA1 WHERE KTOKK = 'ZLIE' ).

But this statment is doing my purpose .

My requirement is , I need to update ZDATE_FLAG = SPACE in ZFINAL_DATA table when LFA1 -LIFNR = ZFINAL_DATA-ZGLOBAL_CODE and LFA1-KTOKK = 'ZLIE'.

Can any correct the update statment which will be helpfull to proceed further .

Regards,

Sriram

[Former Member]

Hi Sriram,

SELECT single LIFNR FROM LFA1  into w_lifnr WHERE KTOKK = 'ZLIE' .

data : w_ ZGLOBAL_CODE   type ZFINAL_DATA -ZGLOBAL_CODE .

  w_ ZGLOBAL_CODE = w_lfinr.

UPDATE ZFINAL_DATA  SET ZDATE_FLAG = SPACE 
where ZGLOBAL_CODE  = w_ ZGLOBAL_CODE .
if sy-subrc  = 0.
  commit work.
else.
  roll back.
endif.

Regards,

Prabhudas

[Former Member]

Hi Prabhudas,

Tnks for quick responce .My LFA1 has lakhs of vendors , so i need to do same validation in the Update statment.so let me that possiblites.

Regards,

Sri

[Former Member]

Hi Sriram,

data : begin of t_lfa1 occurs 0,
             lifnr type lfa1-lifnr,
        end of t_lfa1.

SELECT  LIFNR FROM LFA1  into table t_lfa1 WHERE KTOKK = 'ZLIE' .
 
data : w_ ZGLOBAL_CODE   type ZFINAL_DATA -ZGLOBAL_CODE .

loop at t_lfal. 

   w_ ZGLOBAL_CODE = t_lfa1-lifnr.
 
   UPDATE ZFINAL_DATA  SET ZDATE_FLAG = SPACE 
          where ZGLOBAL_CODE  = w_ ZGLOBAL_CODE .
        if sy-subrc  = 0.
           commit work.
       else.
         roll back.
        endif.
 endloop.

Regards,

Prabhudas

[Former Member]

Hi Prabhu,

I am searching the possiblites which i can do validtion in update statment itslef , instead of writing seperate query on LFA1 trable.

Regards,

Sri

[Former Member]

you cannot do validation on update query..

validation is nothing but sucess commit work not sucess no record exists rollback.

then you need to read the table ZFINAL_DATA with SET ZDATE_FLAG ne sapce

with select query and cross check with LFal table.

[Former Member]

Hi,

Hw can i use more than one WHERE condtion in UPDATE statment....

Regards,

Sri

[Former Member]

yes

but do not use and

same as read put sapce

example :--

UPDATE ZFINAL_DATA SET ZDATE_FLAG = SPACE

where ZGLOBAL_CODE = w_ ZGLOBAL_CODE

ZDATE_FLAG ne SPACE .

just keep on adding field in where condition but dont put and operator inbetween the two fields...

Prabhudas

Edited by: Prabhu Das on May 20, 2009 12:04 AM

[Former Member]

Hi Sriram,

Have a look into the logic of the below query. Maybe this will fulfil the requirement.

TYPES: BEGIN OF lt_lifnr,

lifnr TYPE lifnr,

END OF lt_lifnr.

DATA: la_lifnr TYPE TABLE OF lt_lifnr,

wa_zfinal_data TYPE zfinal_data,

la_zfinal_data TYPE TABLE OF zfinal_data.

TABLES: zfinal_data.

SELECT lifnr

FROM lfa1

INTO TABLE la_lifnr

WHERE ktokk = 'ZLIE'.

SORT la_lifnr BY lifnr.

SELECT *

FROM zfinal_data

INTO TABLE la_zfinal_data.

LOOP AT la_zfinal_data INTO wa_zfinal_data.

READ TABLE la_lifnr WITH KEY lifnr = wa_zfinal_data-zglobal_code

BINARY SEARCH TRANSPORTING NO FIELDS.

IF sy-subrc = 0.

wa_zfinal_data-zdate_flag = space.

MODIFY la_zfinal_data

FROM wa_zfinal_data

INDEX sy-tabix

TRANSPORTING zdate_flag.

ENDIF.

ENDLOOP.

MODIFY zfinal_data FROM TABLE la_zfinal_data.

COMMIT WORK.