Business Key Generation

--000e0cdf70e24f682e047edde665
Content-Type: text/plain; charset=UTF-8

Hi,

I have a table that I need to migrate some data into:

CREATE TABLE `employee` (
`employeeid` bigint(20) NOT NULL auto_increment,
...
`reference` varchar(20) default NULL,
`fkcompanyid` bigint(20) default NULL,
PRIMARY KEY (`employeeid`),
KEY `FK4722E6AE82D7E095` (`fkcompanyid`),
CONSTRAINT `FK4722E6AE82D7E095` FOREIGN KEY (`fkcompanyid`) REFERENCES
`company` (`companyid`),
....
) ENGINE=InnoDB AUTO_INCREMENT=1000000000001585 DEFAULT CHARSET=latin1 |


My question is I want to generate a reference business key (a number) as the
migrated data is blank.

something like

update employee set reference = ROWNUM where fkcompanyid = X

I think in Oracle ROWNUM would help me do this.
I.e. generate a reference based on the position in the result set.

any Ideas ?

--
Tom Goring

--000e0cdf70e24f682e047edde665--

RE: Business Key Generation

WW91ciBhdXRvLWluY3JlbWVudCBjb3VsZCBiZSBlZmZlY3RpdmVseSBhIHJv dyBudW1iZXIuIE90
aGVyd2lzZSB0aGVyZSBpcyB0aGlzIHRlY2huaXF1ZSB3aXRoIHVzZXIgdmFy aWFibGVzOg0KDQpT
RVQgQHJvd251bSA6PSAwOw0KU0VMRUNUIEByb3dudW06PUByb3dudW0rMSBh cyBST1dOVU0sIC4u
LiBGUk9NIC4uLiA7DQoNClJlZ2FyZHMsDQpHYXZpbiBUb3dleQ0KDQoNCi0t LS0tT3JpZ2luYWwg
TWVzc2FnZS0tLS0tDQpGcm9tOiBUb20gR29yaW5nIFttYWlsdG86dG9tLmdv cmluZ0BnbWFpbC5j
b21dDQpTZW50OiBGcmlkYXksIEZlYnJ1YXJ5IDA1LCAyMDEwIDk6MzcgQU0N ClRvOiBteXNxbEBs
aXN0cy5teXNxbC5jb20NClN1YmplY3Q6IEJ1c2luZXNzIEtleSBHZW5lcmF0 aW9uDQoNCkhpLA0K
DQpJIGhhdmUgYSB0YWJsZSB0aGF0IEkgbmVlZCB0byBtaWdyYXRlIHNvbWUg ZGF0YSBpbnRvOg0K
DQpDUkVBVEUgVEFCTEUgYGVtcGxveWVlYCAoDQogIGBlbXBsb3llZWlkYCBi aWdpbnQoMjApIE5P
VCBOVUxMIGF1dG9faW5jcmVtZW50LA0KLi4NCiAgYHJlZmVyZW5jZWAgdmFy Y2hhcigyMCkgZGVm
YXVsdCBOVUxMLA0KICBgZmtjb21wYW55aWRgIGJpZ2ludCgyMCkgZGVmYXVs dCBOVUxMLA0KICBQ
UklNQVJZIEtFWSAgKGBlbXBsb3llZWlkYCksDQogS0VZIGBGSzQ3MjJFNkFF ODJEN0UwOTVgIChg
Zmtjb21wYW55aWRgKSwNCkNPTlNUUkFJTlQgYEZLNDcyMkU2QUU4MkQ3RTA5 NWAgRk9SRUlHTiBL
RVkgKGBma2NvbXBhbnlpZGApIFJFRkVSRU5DRVMNCmBjb21wYW55YCAoYGNv bXBhbnlpZGApLA0K
Li4uDQopIEVOR0lORT1Jbm5vREIgQVVUT19JTkNSRU1FTlQ9MTAwMDAwMDAw MDAwMTU4NSBERUZB
VUxUIENIQVJTRVQ9bGF0aW4xIHwNCg0KDQpNeSBxdWVzdGlvbiBpcyBJIHdh bnQgdG8gZ2VuZXJh
dGUgYSByZWZlcmVuY2UgYnVzaW5lc3Mga2V5IChhIG51bWJlcikgYXMgdGhl DQptaWdyYXRlZCBk
YXRhIGlzIGJsYW5rLg0KDQpzb21ldGhpbmcgbGlrZQ0KDQp1cGRhdGUgZW1w bG95ZWUgc2V0IHJl
ZmVyZW5jZSA9IFJPV05VTSB3aGVyZSBma2NvbXBhbnlpZCA9IFgNCg0KSSB0 aGluayBpbiBPcmFj
bGUgUk9XTlVNIHdvdWxkIGhlbHAgbWUgZG8gdGhpcy4NCkkuZS4gZ2VuZXJh dGUgYSByZWZlcmVu
Y2UgYmFzZWQgb24gdGhlIHBvc2l0aW9uIGluIHRoZSByZXN1bHQgc2V0Lg0K DQphbnkgSWRlYXMg
Pw0KDQotLQ0KVG9tIEdvcmluZw0KDQpUaGlzIG1lc3NhZ2UgY29udGFpbnMg Y29uZmlkZW50aWFs
IGluZm9ybWF0aW9uIGFuZCBpcyBpbnRlbmRlZCBvbmx5IGZvciB0aGUgaW5k aXZpZHVhbCBuYW1l
ZC4gIElmIHlvdSBhcmUgbm90IHRoZSBuYW1lZCBhZGRyZXNzZWUsIHlvdSBh cmUgbm90aWZpZWQg
dGhhdCByZXZpZXdpbmcsIGRpc3NlbWluYXRpbmcsIGRpc2Nsb3NpbmcsIGNv cHlpbmcgb3IgZGlz
dHJpYnV0aW5nIHRoaXMgZS1tYWlsIGlzIHN0cmljdGx5IHByb2hpYml0ZWQu ICBQbGVhc2Ugbm90
aWZ5IHRoZSBzZW5kZXIgaW1tZWRpYXRlbHkgYnkgZS1tYWlsIGlmIHlvdSBo YXZlIHJlY2VpdmVk
IHRoaXMgZS1tYWlsIGJ5IG1pc3Rha2UgYW5kIGRlbGV0ZSB0aGlzIGUtbWFp bCBmcm9tIHlvdXIg
c3lzdGVtLiBFLW1haWwgdHJhbnNtaXNzaW9uIGNhbm5vdCBiZSBndWFyYW50 ZWVkIHRvIGJlIHNl
Y3VyZSBvciBlcnJvci1mcmVlIGFzIGluZm9ybWF0aW9uIGNvdWxkIGJlIGlu dGVyY2VwdGVkLCBj
b3JydXB0ZWQsIGxvc3QsIGRlc3Ryb3llZCwgYXJyaXZlIGxhdGUgb3IgaW5j b21wbGV0ZSwgb3Ig
Y29udGFpbiB2aXJ1c2VzLiBUaGUgc2VuZGVyIHRoZXJlZm9yZSBkb2VzIG5v dCBhY2NlcHQgbGlh
YmlsaXR5IGZvciBhbnkgbG9zcyBvciBkYW1hZ2UgY2F1c2VkIGJ5IHZpcnVz ZXMgb3IgZXJyb3Jz
IG9yIG9taXNzaW9ucyBpbiB0aGUgY29udGVudHMgb2YgdGhpcyBtZXNzYWdl LCB3aGljaCBhcmlz
ZSBhcyBhIHJlc3VsdCBvZiBlLW1haWwgdHJhbnNtaXNzaW9uLiBbRnJpZW5k RmluZGVyIE5ldHdv
cmtzLCBJbmMuLCAyMjAgSHVtYm9sdCBjb3VydCwgU3Vubnl2YWxlLCBDQSA5 NDA4OSwgVVNBLCBG
cmllbmRGaW5kZXIuY29tDQo=