Hi Aristedes, You can try the following:
1. Rename to old table (prevents usage until the new table is ready) 2. Create your new table structure with the identity column with a temporary name. 3. Copy data from the old table to the new table with a single INSERT ... SELECT statement. 4. Rename the temp table to the original table name. This will fulfill the requirement you specified and also probably also run faster. Cheers, Matt On 3/21/11 3:05 AM, "Aristedes Maniatis" <[email protected]> wrote: > I have an existing Derby database to which I want to add a new column which > will be filled with unique (monotonically increasing) values. Now, for new > records being added to the database that is easy: an identity column will do > that nicely. But I want to update all the existing rows in the database. > > Ideally I want to write SQL which looks like: > > UPDATE Artist set artistNum = ... WHERE artistNum is NULL > > But I can't see how to do this. In MySQL I'd be using variables which > increment for each row written. In Derby is the only solution to use a > Procedure? Are there any examples of something similar: I can see how to write > a procedure, but couldn't find any examples of how to update lots of rows of > data like this. Am I missing the point? > > > Thanks > Ari > iCrossing Privileged and Confidential Information This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information of iCrossing. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
