update key field ube

[nkuebelbeck]

say i've got a table with 4 fields. two of which make a unique record, and have an index

Fields A,B,C,D (A,B unique index)

given a four records

before ube execution
A|B|C|D
1) 1 2 3 4
1) 1 3 5 4
1) 1 4 6 4
1) 1 5 7 4

I select in my ube dataselection where A==1

I want to update each B field B+=10

desired after ube execution
A|B|C|D
1) 1 20 3 4
1) 1 30 5 4
1) 1 40 6 4
1) 1 50 7 4

now, I don't think I can do this using event rules unless I delete the record and insert a new record. Is that the only way?

My only other thought would be to do an assignment during to DO event and assign the BC value to the new value, but I'm unsure if this would work.

thanks

 

[BOster]

I think a delete/insert would probably be the way to go especially since field B is effectively a calculated field. In other words you can't effectively do the following in JDE (I don't think???):

Update F59MYTBL set B=(B * 10) where A=1

So a delete/insert might be the most straight forward way to effectively "update" the record.


If you were setting a field to a specific value I do believe you can effectively do the following in JDE (even if the field is a part of the PK, provided a key violation doesn't happen):

Update F59MYTBL set A=2 where A=1

 

[johndanter]

I maybe wrong as something tells me it isn't possible, as the key is the key.....but.................. something is niggling in my head saying it might be

I just think you need another index to do the update.

So I think if you had an index of A C D and used that to update the B field you maybe ok.....?

Maybe

If not what Brian has said is the way forward


Found this on google http://www.deepeshmd.com/enterpriseone/update-your-primary-keys-from-jde/

 

[David Robertson]

The way that JDE generates the update SQL, you can't both select the row with a key value and update that value.
If you can identify the row uniquely using some other values, then you can make a key from that and use it to update the value.
For example, if the table was F0901, you can update the MCU by using the AID key instead.

If you try to do this using a GC or BC auto update, I think it'll actually just insert new rows.

Delete and insert are probably the easiest, otherwise, I'd probably make a simple C function that would accept the "where" variables separately to the "set" variable.

I know I have done a similar thing in the past, but really can't remember as it's a rare thing to need to do. Those tables that occasionally need this kind of think usually also include a UKID column as an alternative so that you can do exactly this.

EDIT: John's link to Deepesh's site seems to have the answer if you want to use a form to do it, but be sure of the last note he makes "make sure, that you validate or disable the Primary key columns for Headerless Detail Form’s UPDATE MODE"

 

[DBohner-(db)]

NK,

The other suggestion are spot on.

What's not covered is - is this a one-time global update, or is this on an as-needed basis.

If global, and there is a lot of rows - one might consider an In-Place Table Conversion.
Generally, Conversions use TC to migrate a prior version's table format to the target versions table format - this includes changing primary keys and primary key formats. The In-Place Conversion creates a temporary table with the new format, converts the data to the new format, deletes the source table when conversion is complete - then renames the Temp table to the original table name.

What I don't know - if the source and target structures are the same, will the process work? If it will work, you would follow the normal TC In-Place process and in the TC's ER you will put A=A, B=B*10, C=C and D=D...

Just scheming - I would only consider if the other solutions failed.

Best Solution is the Create new Record then Delete Old Record approach.

(db)

 

[nkuebelbeck]

Thought I had replied with my solution here.

I made a ube that inserts a new records and i deleted the old records manually.

fyi this was over F0005 and we decided to change the UDC 31/RC so I also had to update the F31122 with the new code.(sub section join, then table io update using F31122.ukid)

hope this helps somebody
originally when I tried to delete/insert i was getting weird behavior almost as if it was processing the inserts in the ube after they were inserted. strange. but i figured as much when trying to update key fields.