topic Update statement in Application Development and Automation Discussions

Update statement

Former Member — Fri, 30 Jun 2006 09:43:57 GMT

Hi,

I have an internal table which contains fields which are a part of a database table. i.e if database table has 10 fields, my work area has 3 fields.

Can I have statement like

update <tab>

from table <itab>

set k1 = f1

where k = f.

Regards,

Madhu

Re: Update statement

Former Member — Fri, 30 Jun 2006 09:47:08 GMT

Yes you can do that. However, if you are trying to update a master table, then you should not do that. All the standard tables should be updated only using SAP programs.

Regards,

Ravi

Note: Please mark the helpful answers

Re: Update statement

Former Member — Fri, 30 Jun 2006 09:47:26 GMT

Hi,

These are the only option available with update .

1. UPDATE dbtab SET f1 ... fn. or

UPDATE (dbtabname) SET f1 ... fn.

2. UPDATE dbtab FROM wa. or

UPDATE (dbtabname) FROM wa.

3. UPDATE dbtab FROM TABLE itab. or

UPDATE (dbtabname) FROM TABLE itab.

4. UPDATE dbtab. or

UPDATE *dbtab.

Re: Update statement

Former Member — Fri, 30 Jun 2006 09:52:35 GMT

Hi,

Yes you can do that, Read the Help on Update.

Regards

vijay

Re: Update statement

Former Member — Fri, 30 Jun 2006 10:13:47 GMT

Hi madhukeshwar,

1. I would personally prefer this kind of concept.

2. myitab ( 3 fields )

ZTAB (internal table like ZDBTAB)

3. SELECT SINGLE *

FROM zdbtab

into ztab

where primarykey = myitab-field1.

if sy-subrc = 0 .

move-corresponding MYITAB to ZTAB.

MODIFY ZDBTAB FROM ZTAB.

endif.

regards,

amit m.

Re: Update statement

Former Member — Fri, 30 Jun 2006 10:26:43 GMT

It is OK. But I think you better use 'MODIFY' command. That will be more robust in this scenario.

Re: Update statement

Former Member — Fri, 30 Jun 2006 11:03:06 GMT

Hi Ravi,

It is a Master table. We appended 4 fields. Now we need to populate those. So how can we go ahead?

Re: Update statement

Former Member — Fri, 30 Jun 2006 11:05:49 GMT

HI again,

1. u added 4 fields to an existing table.

and u want to fill them using some program.

2. use this kind of logic.

*----

data : ztab like table of ztable.

select * from ztable

into ztab.

*----

LOOP AT ZTAB.

ZTAB-FIELD15 = 'AA'.

ZTAB-FIELD16 = 'BB'.

ZTAB-FIELD17 = 'CC'.

ZTAB-FIELD18 = 'DD'.

MODIFY ZTABLE FROM ZTAB.

ENDLOOP.

*----

regards,

amit m.

Re: Update statement

Former Member — Fri, 30 Jun 2006 11:12:42 GMT

define an internal table of same structure of your Ztable.

1. read all the entries from your Ztable to internal table.

2. modify the internal table with the values for those newly created 4 fields.

3. modify Ztable from the internal table.

data: itab type standard table of ZTABLE WITH HEADER LINE.

select *

from ztable

into table itab.

if sy-subrc = 0.

loop at itab.

itab-newfield1 = <some value>

itab-newfield2 = <some value2>

*etc...

modify itab index sy-tabix.

endloop.

modify ztable from table itab.

if sy-subrc = 0.

write 😕 'no of records updated' , sy-dbcnt.

endif.

regards

srikanth

Regards

srikanth

Message was edited by: Srikanth Kidambi

Re: Update statement

Former Member — Fri, 30 Jun 2006 11:19:15 GMT

What I meant was standard fields should not updated directly. You can directly update the custom fields.

Regards,

ravi

Note: Please mark all the helpful answers

Re: Update statement

Former Member — Fri, 30 Jun 2006 12:14:24 GMT

Thats what I wanted. Is there any spcific way or as amit told we need to select every thing and put the custom field values and put everything back?

Is there any way with which just we update custom fields

--without selecting every field

--update at once (one DB hit) using internal table

Regards,

Madhu

Re: Update statement

Former Member — Fri, 30 Jun 2006 12:19:34 GMT

Hi again,

1. Is there any way with which just we update custom fields

--without selecting every field

Yes, we can do like this.

update ZT001

set bukrs = '1000'

butxt = 'My company'

where bukrs = '1000'.

2. update at once (one DB hit) using internal table

I am not sure,

but we have to do record by record.

regards,

amit m.

Re: Update statement

Former Member — Tue, 04 Jul 2006 12:12:33 GMT

Thanks.

Since this is a standard SAP table, can we update it without taking precautions (such as lock)? Is there a standard way where in we can see to it that data consistancy is not lost?

Regards,

Madhu

Re: Update statement

Former Member — Tue, 04 Jul 2006 12:15:07 GMT

If is SAP table, you should NOT update it explicitly at all. You should use the BAPI / functions to do the same.

Which table are you updating? Did you try to see if a BAPI does the same?

Regards,

Ravi

Re: Update statement

Former Member — Tue, 04 Jul 2006 12:36:36 GMT

I have added 4 zfields to the table JVTBEZART. Those z fields I am updating.

Re: Update statement

Former Member — Tue, 04 Jul 2006 12:39:09 GMT

hi madhu,

sorry , because i don't understand that what do u want to do, plz tell me in detail with ur code.

thanks

chetan vishnoi

Re: Update statement

Former Member — Tue, 04 Jul 2006 13:34:10 GMT

Hai Bhut

Check the following Document for Update Statement

UPDATE

Variants

1. UPDATE dbtab SET s1 ... sn.

2. UPDATE dbtab. or

UPDATE *dbtab. or

UPDATE (dbtabname) ... .

3. UPDATE dbtab FROM TABLE itab. or

UPDATE (dbtabname) FROM TABLE itab.

Effect

Updates values in a database table (see Relational database ). You can specify the name of the database table either directly in the form dbtab or at runtime as contents of the field dbtabname . In both cases, the table must be known to the ABAP/4 Dictionary . If you specify the name of the database table directly, the program must also contain an appropriate TABLES statement. Normally, lines are updated only in the current client. Data can only be updated using a view if the view refers to a single table and was created in the ABAP/4 Dictionary with the maintenance status "No restriction".

UPDATE belongs to the Open SQL command set.

Notes

Authorization checks are not supported by the UPDATE statement. You must include these in the program yourself.

Changes to lines made with the UPDATE command only become final after a database commit (see LUW ). Prior to this, any database update can be canceled by a database rollback (see Programming transactions ).

In the dialog system, you cannot rely on the database system locking mechanism alone to synchronize simultaneous access to the same database by several users. Therefore, it is often necessary to use the SAP locking mechanism .

Variant 1

UPDATE dbtab SET s1 ... sn.

Additions

1. ... WHERE condition

2. ... CLIENT SPECIFIED

Effect

Updates values in a database table. If there is no WHERE clause , all lines (in the current client) are updated. If a WHERE condition is specified, only those records which satisfy the condition are updated.

The SET clause s1 ... sn identifies the columns to be updated and assigns values to them. Three types of SET statements si are supported:

f = g In all selected lines, the database table column determined by f receives the values of the ABAP/4 field or literal g .

f = f + g In all selected lines, the contents of the ABAP/4 field or literal g is added to the value in the database table column determined by f . The NULL value remains unchanged. This statement can only be applied to a numeric field.

f = f - g In all selected lines, the contents of the ABAP/4 field or literal g is subtracted from the value in the database table column determined by f . The NULL value remains unchanged. This statement can only be applied to a numeric field.

When the command has been executed, the system field SY-DBCNT contains the number of updated lines.

The return code value is set as follows:

SY-SUBRC = 0 At least one line was updated,

SY_SUBRC = 4 No line was updated because no line could be selected.

Note

With pooled and cluster tables, an UPDATE cannot change any primary key field.

Examples

Update discount for all customers (in the current client) to 3 percent:

TABLES SCUSTOM.

UPDATE SCUSTOM SET DISCOUNT = '003'.

Note

The 'colon and comma' logic in the program fragment

UPDATE SCUSTOM SET: DISCOUNT = '003',

TELEPHONE = '0621/444444'

WHERE ID = '00017777'.

defines record chains,

not through a single statement which updates the discount and the telephone number of the customer with the customer number '00017777',

but by means of two statements where the first updates the discount for all customers and the second changes the telephone number of the customer with the customer number '00017777'.

Addition 1

... WHERE condition

Effect

Updates only those lines which satisfy the WHERE clause condition .

Example

Increase the number of occupied seats on Lufthansa flight 0400 on 28.02.1995 by 3 (in the current client):

TABLES SFLIGHT.

UPDATE SFLIGHT SET SEATSOCC = SEATSOCC + 3

WHERE CARRID = 'LH' AND

CONNID = '0400' AND

FLDATE = '19950228'.

Addition 2

... CLIENT SPECIFIED

Effect

Switches off automatic client handling. This allows you to update across all clients when using client-specific tables. The client field is treated like a normal table field, for which you can formulate suitable conditions in the WHERE clause.

This addition must immediately follow the database table name.

Example

Increase the number of occupied seats on Lufthansa flight 0400 on 28.02.1995 by 3 in client 2:

TABLES SFLIGHT.

UPDATE SFLIGHT CLIENT SPECIFIED

SET SEATSOCC = SEATSOCC + 3

WHERE MANDT = '002' AND

WHERE CARRID = 'LH' AND

CONNID = '0400' AND

FLDATE = '19950228'.

Variant 2

UPDATE dbtab. or

UPDATE *dbtab. or

UPDATE (dbtabname) ... .

Additions

1. ... FROM wa

2. ... CLIENT SPECIFIED

Effect

These are SAP-specific short forms which update one single line of a database table. The primary key for identifying the line to be updated and the values to be changed when specifying the database table name in the program are taken from the table work area - dbtab or *dbtab . If the database table name is determined at runtime, you need to use the addition ... FROM wa .

When the command has been executed, the system field SY-DBCNT contains the number of updated lines (0 or 1).

The return code value is set as follows:

SY-SUBRC = 0 The specified line was updated,

SY_SUBRC = 4 No line was updated because no line with the specified primary key exists.

Examples

Update discount for the customer with the customer number '00017777' to 3 percent (in the current client):

TABLES SCUSTOM.

SCUSTOM-ID = '00017777'.

SCUSTOM-DISCOUNT = '003'.

UPDATE SCUSTOM.

Addition 1

... FROM wa

Effect

Takes the values for the line to be updated not from the table work area dbtab , but from the explicitly specified work area wa . Here, the data is taken from wa , moving from left to right according to the structure of the table work area dbtab (defined with TABLES ). Since the structure of wa is ignored, the work area wa must be at least as wide (see DATA ) as the table work area dbtab and the alignment of the work area wa must correspond to the alignment of the table work area. Otherwise, a runtime error occurs

Example

Update the telephone number of the customer with the customer number '12400177' in the current client:

TABLES SCUSTOM.

DATA WA LIKE SCUSTOM.

WA-ID = '12400177'.

WA-TELEPHONE = '06201/44889'.

UPDATE SCUSTOM FROM WA.

Note

If you do not explicitly specify a work area, the values for the line to be updated are taken from the table work area dbtab , even if the statement appears in a FORM or FUNCTION where the table work area is held in a formal parameter or a local variable.

Addition 2

... CLIENT SPECIFIED

Effect

Like variant 1.

Variant 3

UPDATE dbtab FROM TABLE itab. or

UPDATE (dbtabname) FROM TABLE itab.

Addition

... CLIENT SPECIFIED

Effect

Mass update of several lines in a database table. Here, the primary key for identifying the lines to be updated and the values to be changed are taken from the lines of the internal table itab . The lines of the internal table must satisfy the same conditions as the work area wa in addition 1 to variant 2.

The system field SY-DBCNT contains the number of updated lines, i.e. the number of lines in the internal table itab which have key values corresponding to lines in the database table.

The return code value is set as follows:

SY-SUBRC = 0 All lines from itab could be used to update the database table.

SY_SUBRC = 4 At least one line of the internal table itab in the database table, had no line with the same primary key. The other lines of the database table were updated.

Note

If the internal table itab is empty, SY-SUBRC and SY-DBCNT are set to 0.

Addition

... CLIENT SPECIFIED

Thanks & regards

Sreenivasulu P

Re: Update statement

Former Member — Tue, 04 Jul 2006 13:37:15 GMT

Hi Chetan,

I there is a SAP table jvtbezart. I have appended 4 Z fields. Those Z fields I need to update in the table.

Thank you.

Regards,

Madhu