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

modify database table

Former Member — Wed, 18 Oct 2006 14:24:28 GMT

Hi all,

I've a query regarding the modifying of a data base table directly ( that means without taking the values into the internal table) for a field, particulary when the field is a primary key.

eg: in a field called RICE the initial value is given as '120'. now i want to change it into a new value 'SAP'.

this RICE field is made as a primary key.

please tell me the solution.

Sailaja.

Re: modify database table

Former Member — Wed, 18 Oct 2006 14:26:38 GMT

Please use UPDATE

UPDATE (table)SET RICE = 'SAP'

where condition

Re: modify database table

Former Member — Wed, 18 Oct 2006 14:28:23 GMT

You cannot change the value of a key field. Select to internal table, update data in internal table and insert into db. Then delete records initially selected.

Re: modify database table

Former Member — Wed, 18 Oct 2006 14:31:52 GMT

Instead of updating the key field, you can add one more record ans delete the existing one which makes more sense.

insert ztab from ztab.

delete ztab where key = '120'.

Regards,

ravi

Re: modify database table

Former Member — Wed, 18 Oct 2006 14:36:33 GMT

Sailaja,

Michael's statement is not, in fact, true in all cases. It depends on the underlying DB system. I just changed a primary key in one of custom tables.

HOWEVER, it is wonderful advice. The primary key should be a unique identifer of a record. IF a record's unique identifier changes (which SHOULD NOT be the case if the table is built cleanly/correctly), you should follow Michael's advice. Create a new record with the new key and then delete the previous.

Re: modify database table

Former Member — Wed, 18 Oct 2006 14:45:59 GMT

thanks for all ur reply's.even i tried doing the same like inseting and later deleting but my client dont want me to delete the records...

Re: modify database table

Former Member — Wed, 18 Oct 2006 14:55:44 GMT

Not much alternative. Is this a custom table?

Rob

Re: modify database table

Former Member — Wed, 18 Oct 2006 15:29:30 GMT

Sailaja,

Then your client should NOT have a table with key fields that are prone to change!! That is a bad design. I assume that this is a custom table?

And their request to you is, in practice, "deleting" the record... once a primary key is altered... the initial record's uniqueness has been destroyed.

Don't forget points for helpful answers as well.

Re: modify database table

Former Member — Wed, 18 Oct 2006 15:36:51 GMT

I find this odd. I thought the whole idea behind open SQL is that it should behave the same no matter what the type of database.

What database are you using? You didn't use native SQL to do the update did you?

Rob

Re: modify database table

Former Member — Wed, 18 Oct 2006 15:37:53 GMT

If you change the primary key and update the table it would insert a new record, hence you would need to delete the old record unless you wish to keep in the table with some flag indicator.

Apart from the above there is no other alternative.

Re: modify database table

Former Member — Wed, 18 Oct 2006 15:45:49 GMT

Sailaja,

IF this is a core table (not a custom Y or Z table)... do not perform this request. You run a high risk of damaging the referential integrity of the client's database.

If it is a core table, take this request to your manager for assistance... this client will need some "Database 101" training first.

Re: modify database table

Former Member — Wed, 18 Oct 2006 16:05:17 GMT

Rob,

Was this question for Sailaja?

"I find this odd. I thought the whole idea behind open SQL is that it should behave the same no matter what the type of database.

What database are you using? You didn't use native SQL to do the update did you?"

Re: modify database table

Former Member — Wed, 18 Oct 2006 16:10:34 GMT

Sorry John - it was for you.

Rob

Re: modify database table

Former Member — Wed, 18 Oct 2006 16:22:23 GMT

No it was performed thru Open SQL.

Open SQL is a database interface to your DB engine. It creates/generates native SQL that your DB engine understands. It does not, however, enforce all of the rules, settings, etc that your DBAs have set up at the "true" database layer of the R/3 client-server model.

Re: modify database table

Former Member — Wed, 18 Oct 2006 16:23:05 GMT

We are using Oracle by the way.

Re: modify database table

Former Member — Wed, 18 Oct 2006 16:32:29 GMT

John - that would mean that ABAP code behaves differently for different databases and therefore not "transportable".

We were on Oracle up to a couple of years ago and it wouldn't let us change primary keys. Are you saying that there are database settings that would allow this? (And that the SAP kernel can read to allow or disallow changes to primary keys?)

Rob

Re: modify database table

Former Member — Wed, 18 Oct 2006 16:40:42 GMT

Rob,

"that would mean that ABAP code behaves differently for different databases and therefore not "transportable"."

ABAP code behaves the same... the underlying objects, in this case, the DB engine itself behaves according to the parameters that the DBAs have set - which can be different from implementation to implementation.

And this setting is outside of the SAP kernel... it is truly on the DB itself. Bear in mind that this is 3-tiered client-server model... and each piece can be adjusted (sometimes without the other pieces "knowing").

Re: modify database table

Former Member — Wed, 18 Oct 2006 16:51:35 GMT

John - but you said that you changed the key. The rest of us can't. Or am I just not understanding?

Rob

Re: modify database table

Former Member — Wed, 18 Oct 2006 17:02:38 GMT

John - <i>mea culpa</i>. I just did what I thought was not possible. I created a table, inserted a record and then changed the key field of that record (DB2).

rob

Re: modify database table

Former Member — Wed, 18 Oct 2006 17:05:49 GMT

Enjoy.

It is probably NOT a good thing... but it is possible b/c the DB is TRULY separated from SAP R/3.