Changing serial value
marko kopac
I don't know how to change a serial value in a table (Informix
database). I need this to copy a record to the same table. First I copy
a record to a temp table. There I try to change the serial value and
then to insert to the original table. This is my idea. Has anybody some
another idea hove to solve this problem?
Marko
Dave Otto
If you want to be able to control the value inserted, you need to
use a different data type. If you are just trying to clone a
record, exclude the "serial" column from the insert. You cannot
specify the value of a serial column.
-d
Nigel Gall
At 12:19 PM 8/19/97 EDT, you wrote:
>
>If you want to be able to control the value inserted, you need to
>use a different data type. If you are just trying to clone a
>record, exclude the "serial" column from the insert. You cannot
>specify the value of a serial column.
>
what version are we talking of here? i seem to recall abt 4 years ago at
my alma mater we were able to insert values to a serial column, by
specifying a value other than zero. can't remember what the consequences
were in terms of the next generated number. the versions were SE and 4GL
RDS 4.10.
>-d
>
>At 10:14 AM 8/19/97 +0200, marko kopac wrote:
>>I don't know how to change a serial value in a table (Informix
>>database). I need this to copy a record to the same table. First I copy
>>a record to a temp table. There I try to change the serial value and
>>then to insert to the original table. This is my idea. Has anybody some
>>another idea hove to solve this problem?
>>
>>Marko
>>
>>
Best regards,
Nigel
+-------------------------------------------------------------+
|Name : Edmund Nigel Gall Tel: (868) 636 3153 |
|Title : Information Systems Specialist Fax: (868) 679 3770 |
|Company: Process Plant Services Limited |
|Address: Atlantic Avenue, Point Lisas Industrial Estate |
| Point Lisas, Couva, Trinidad & Tobago, W.I. |
+----- mailto:nig...@ppsl.com ------ http://www.ppsl.com -----+
Art S. Kagel
marko kopac wrote:
>
> I don't know how to change a serial value in a table (Informix
> database). I need this to copy a record to the same table. First I copy
> a record to a temp table. There I try to change the serial value and
> then to insert to the original table. This is my idea. Has anybody some
> another idea hove to solve this problem?
table the temp table will be created with a serial column and you still
will not be able to update the serial number. Just create the temp
table explicitely with an integer rather than serial and insert into it.
Ex:
create temp table copy_cache {
was_serial integer,
...
};
insert into copy_cache select * from orig_table where ... ;
update copy_cache set was_serial = 12345 where was_serial = ... ;
^^^^^ make sure this does not exist
in the original table.
insert into orig_table select * from was_serial;
drop table caopy_cache;
Art S. Kagel
John Kasprzyk
----- Begin Included Message -----
If you want to be able to control the value inserted, you need to
use a different data type. If you are just trying to clone a
record, exclude the "serial" column from the insert. You cannot
specify the value of a serial column.
-d
At 10:14 AM 8/19/97 +0200, marko kopac wrote:
>I don't know how to change a serial value in a table (Informix
>database). I need this to copy a record to the same table. First I copy
>a record to a temp table. There I try to change the serial value and
>then to insert to the original table. This is my idea. Has anybody some
>another idea hove to solve this problem?
>
>Marko
>
>
----- End Included Message -----
This is not correct. You can insert a row with a serial column
specified as 0, to generate a serial value, or, with a value specified.
Try it!
Jonathan Leffler
>At 10:14 AM 8/19/97 +0200, marko kopac (marko...@mais.si) wrote:
>>I don't know how to change a serial value in a table (Informix
>>database). I need this to copy a record to the same table. First I copy
>>a record to a temp table. There I try to change the serial value and
>>then to insert to the original table. This is my idea. Has anybody some
>>another idea hove to solve this problem?
>Date: Tue, 19 Aug 1997 06:47:57 -0700
>From: Dave Otto <do...@themoneystore.com>
>X-Informix-List-Id: <list.16024>
>
>If you want to be able to control the value inserted, you need to
>use a different data type. If you are just trying to clone a
>record, exclude the "serial" column from the insert. You cannot
>specify the value of a serial column.
I think Dave's answer is slightly misleading, so I'm going to try to
clarify it.
First of all, you cannot update the value of a serial column:
-232: A SERIAL column(s) may not be updated.
Marko was trying to copy a record to the same table by copying a record
from the table into a temp table, and then re-inserting the record into
the main table. This can be done provided that the insertion back into
the main table specifies 0 for the serial column value:
SELECT * FROM TableA WHERE PKey = pkvalue
INTO TEMP T1;
INSERT INTO TableA
SELECT 0, Column2, Column3, ...
FROM T1;
DROP TABLE T1;
Dave says you cannot specify the value of a serial column. This isn't
entirely accurate. You can specify a serial value on INSERT:
CREATE TEMP TABLE T (S SERIAL(1000000) NOT NULL PRIMARY KEY);
INSERT INTO T VALUES(1); -- OK, inserts row 1
INSERT INTO T VALUES(0); -- OK, inserts row 1000000
INSERT INTO T VALUES(1000000); -- Fails, dup entry in primary key
INSERT INTO T VALUES(1000001); -- OK, inserts row 1000001
INSERT INTO T VALUES(2000000); -- OK, inserts row 2000000
INSERT INTO T VALUES(0); -- OK, inserts row 2000001
INSERT INTO T VALUES(2); -- OK, inserts row 2
You can also insert negative numbers. If you insert 2**31-2, then 0,
the serial numbers recycle starting at 1 on the next insert (but don't
try 2**31-1; on many older versions of the engines, it won't work
properly). Note that a SERIAL column needs a unique index on it to
enforce the uniqueness constraint -- that's what the primary key
declaration does. If you omit it, then the column allows duplicates.
Yours,
Jonathan Leffler (jo...@informix.com) #include <disclaimer.h>
PS: Warning I do not reply to messages with anti-spam in the return path.
Frido van Orden
Suppose you have a table employee (emp_id serial, name char(10), salary
integer)
select name, salary
from employee
where emp_id = 10
into temp mytemptable;
insert into employee(name, salary)
select name, salary from mytemptable;
drop table mytemptable;
Informix detects during the insert that the serial value is missing and
supplies one itself.
The following insert works as well:
insert into employee(emp_id, name, salary)
select 0, name, salary from mytemptable;
> -----Original Message-----
> From: marko kopac [SMTP:marko...@mais.si]
> Posted At: Tuesday, August 19, 1997 10:15 AM
> Posted To: informix
> Conversation: Changing serial value
> Subject: Changing serial value
>
> I don't know how to change a serial value in a table (Informix
> database). I need this to copy a record to the same table. First I
> copy
> a record to a temp table. There I try to change the serial value and
> then to insert to the original table. This is my idea. Has anybody
> some
> another idea hove to solve this problem?
>
> Marko
Mike Segel
Dave Otto wrote:
>
> If you want to be able to control the value inserted, you need to
> use a different data type. If you are just trying to clone a
> record, exclude the "serial" column from the insert. You cannot
> specify the value of a serial column.
>
>
Uhmm, sure you can.
If you want Informix to set the serial valuse, insert the
column with the serial value set to 0.
If you insert a record with any other value (Not Null), you
will be able to insert the record, if that value in the
serial column is not used in the table, and it is greater that
the current value held in the *serial counter* field. (An unsigned
long somewhere in that table space.)
HTH
-Mikey
Oh, and a caveat. I tested this with an old copy of SE.
So it may or maynot be still true today. (Give it a whirl!)
--
#include <std_disclaimer.h> /* Mike Segel (MS385) */
#include <No_Spam.h>
#ifdef OFFENDED_BY_CONTENT
The author takes no responsibility for this post.
Any resemblence to a coherent rational thought is purely coincidence.
-The Management.
#endif
*****************************
Due to AGIS's Refusal to Act Responsibly
We are blocking all of their domains at the packet level.
This block will exist until AGIS modifies their policies to
conform to existing RFCs and net community standards.
We encourage all ISPs and domain holders to do the same.
*****************************
jo...@rl.is
Here's how I see serial columns:
They're essentially not null integer columns with an added functionality
so that if you insert a zero value the actual value will be one more than
the highest value previously inserted. A table can have only one serial
column. Also, once a value is inserted it can't be changed with "update"
for some reason (tried it in online 7.21). However, the row can be
deleted and reinserted with a different serial value.
Yes, you can specify values of a serial column so you can "clone" tables.
Utilities like dbload and dbimport do this so that tables are recreated
exactly.
You can in fact insert duplicate values if there are no constraints to
oppose it. However, the whole purpose of a serial column is to
automatically provide unique values so they are normally set up as the
primary key or a unique key.
I was also surprised to find that if the serial column is ommitted in an
insert then a value is generated as if zero had been specified (as
opposed to null).
In article <33FF6A...@127.0.0.1>,
-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet
Daniel Wright
I don't really have any new ideas to add to the solutions, but wondered
if anyone else had encountered the following problem and had a solution
to it.
A header table uses a serial column for it's key. The detail table uses
this key to join it to the header. Now what do you do if you must
unload some rows from the 2 tables and insert them into a new database?
In many cases, you will NOT be able to re-use the same serial keys for
the header table as they are potentially already in use by the database
you wish to load them into.
I suppose we could write a program to do this, but I was wondering if
there were any easier way.
marko kopac wrote:
> I don't know how to change a serial value in a table (Informix
> database). I need this to copy a record to the same table. First I
> copy
> a record to a temp table. There I try to change the serial value and
> then to insert to the original table. This is my idea. Has anybody
> some
> another idea hove to solve this problem?
>
> Marko
--
- Danny Wright
danw...@bigfoot.com
#include <stddisclaimers.h>
* God is real, unless declared as an integer.
* Why do programmers get Halloween and Christmas mixed up?
Because 0CT(31) == DEC(25)
* I'd love to change the world, but I can't get into the source code.
* /nev/dull
jo...@rl.is
In article
<2456711A2C389F28.9A467BCA...@library-proxy.airnews.net>,
danw...@bigfoot.com.REMOVE wrote:
>
> I don't really have any new ideas to add to the solutions, but wondered
> if anyone else had encountered the following problem and had a solution
> to it.
>
> A header table uses a serial column for it's key. The detail table uses
> this key to join it to the header. Now what do you do if you must
> unload some rows from the 2 tables and insert them into a new database?
> In many cases, you will NOT be able to re-use the same serial keys for
> the header table as they are potentially already in use by the database
> you wish to load them into.
>
> I suppose we could write a program to do this, but I was wondering if
> there were any easier way.
Not an elegant solution, but simple enough:
Find out the largest serial value in the table to be inserted to, e.g.
"select max(ser_col) from new_master". When you insert the new rows add
that value to the serial values. If the largest value was 999999 then you
could do something like:
insert into new_master select ser_col+999999, ... from old_master;
insert into new_detail select mast_key+999999, ... from old_detail;
Make sure users aren't inserting into the tables while this is going on,
maybe by enclosing things in a transaction and table locks.
----------------------------------------------------------------------
John H. Frantz Power-4gl: Extending Informix-4gl
fra...@centrum.is http://www.rl.is/~john/pow4gl.html
Art S. Kagel
Daniel Wright wrote:
>
> I don't really have any new ideas to add to the solutions, but wondered
> if anyone else had encountered the following problem and had a solution
> to it.
>
> A header table uses a serial column for it's key. The detail table uses
> this key to join it to the header. Now what do you do if you must
> unload some rows from the 2 tables and insert them into a new database?
> In many cases, you will NOT be able to re-use the same serial keys for
> the header table as they are potentially already in use by the database
> you wish to load them into.
>
> I suppose we could write a program to do this, but I was wondering if
> there were any easier way.
That's it, a custom application is the best solution. For a one shot
deal you could just unload the rows ordered by key, determine the
largest key on the other server/database, use awk/perl/C to modify the
unload files with appropriate numbers larger than that by adding a fixed
value to each key, then load header rows followed by detail rows.
Art S. Kagel