db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Martin Proulx <prou...@ca.ibm.com>
Subject Is it possible to "RESTART WITH" IDENTITY columns with the result of an expression?
Date Wed, 08 May 2013 21:07:00 GMT
<font face="Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size="2">Hello,<br><br>I
want to import data in tables that have GENERATED BY DEFAULT AS IDENTITY columns.<br><br>The
plan is to rely on ij and the foreignViews tool to import the tables one by one and then reset
the next value of the identity column using "RESTART WITH".<br><br>I can easily
achieve this with a literal value for the restart value (ALTER TABLE ILM_USERS ALTER ID RESTART
WITH 123;), but I haven't been able to find a way to pass the result of an expression for
the new value.<br><br>Everything I've tried has failed:<br><br>ij&gt;
ALTER TABLE ILM_USERS ALTER ID RESTART WITH (SELECT MAX(ID)+1 FROM ILM_USERS);<br>ERROR
42X01: Syntax error: Encountered "(" at line 1, column 45.<br><br>ij&gt; ALTER
TABLE ILM_USERS ALTER ID RESTART WITH INTEGER('123');<br>ERROR 42X01: Syntax error:
Encountered "INTEGER" at line 1, column 45.<br><br>ij&gt; PREPARE TEST AS
'ALTER TABLE ILM_USERS ALTER ID RESTART WITH ?';<br>ERROR 42X01: Syntax error: Encountered
"?" at line 1, column 45.<br><br><br>Is there a way to somehow pass in a
dynamic value?<br><br>Since I haven't grasped all subtleties of the SQL syntax
yet, I don't know if things don't work because of my invalid syntax or because it is just
not supported.<br><br>Unless someone explains how this can be done, the only solution
I can think of at this point is to create a procedure that would take the next value as a
parameter and perform the "RESTART WITH" alteration in Java...<br><br>Thanks for
your help!<br><br>Martin<br><br><br><br></font>

Mime
View raw message