modify database table

[Former Member]

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.

[Former Member]

Please use UPDATE

UPDATE (table)SET RICE = 'SAP'

where condition

[Former Member]

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.

[Former Member]

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.

[Former Member]

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...

[Former Member]

Not much alternative. Is this a custom table?

Rob

[Former Member]

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.

[Former Member]

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

[Former Member]

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.

[Former Member]

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.

[Former Member]

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?"

[Former Member]

Sorry John - it was for you.

Rob

[Former Member]

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.

[Former Member]

We are using Oracle by the way.

[Former Member]

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

[Former Member]

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").

[Former Member]

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

Rob

[Former Member]

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

[Former Member]

Enjoy.

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

[Former Member]

Did I blow your mind ??

[Former Member]

And don't tell your Sarbanes-Oxley 404 auditors that one.

[Former Member]

You sure did. But there's something else going on here as well. I want to do a bit more digging.

Rob

[Former Member]

Well, it looks as if you can do exactly what you want. The only restriction I can see is from the help on the UPDATE command:

you cannot change the primary key 
when you UPDATE a table for which a synchronous matchcode 
is defined. 

But whether or not you should do this is between you your client and the auditors.

Rob

[Former Member]

But again... you must ask WHY a unique key needs to be destroyed? Any data field like this should NOT be part of a primary key... it should become a non-keyed field. In this case, it seems that the customer has built a poor table. They would be best to look into retiring this table and creating/using a properly, built table with a "nice" primary key. Perhaps a GUID if nothing else... it is unique...

[Former Member]

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

[Former Member]

Rob,

Send any follow-up to:

I think we have used this thread enough today.

Sailaja,

Anything else from your end? If not, please close the thread at your convenience.