Re: SQL-Script question

Dave Snyder (ddx85qr@boots.bell-atl.com) wrote: : Quoting hsc@hslx.jena.thur.de... : } : } I'm currently using Informix Online together with ISQL. I can't solve a : } simply problem: : } I've a database with 89 records and I want to add a new column with inventory- : } numbers. The first value should be 800 and the last one 888. The type of the new : } column could be Smallinteger but not Serial. I want automatically fill out this : } new column with the values between 800 and 888. : } Who knows the solution of this simply problem with ISQL? : } : Using just ISQL??? Well, OK, here goes... : 1) dbschema -d dbname -t tabname tabname.sql : 2) UNLOAD TO tabname.unl SELECT 0, * FROM tabname : 3) DROP TABLE tabname : 4) (modify tabname.sql and insert a SERIAL field as the first column) : 5) isql dbname tabname.sql : 6) LOAD FROM tabname.unl INSERT INTO tabname : 7) UNLOAD TO tabname.unl SELECT * FROM tabname : 8) DROP TABLE tabname : 9) (modify tabname.sql and change the SERIAL field to a SMALLINT field) : 10) isql dbname tabname.sql : 11) LOAD FROM tabname.unl INSERT INTO tabname : Obviously this is not glamorous but you did say "with ISQL". : DAS : -- : Dave Snyder @ Bell Atlantic - Philadelphia, PA : WORK: ddx85qr@boots.bell-atl.com HOME: dave@das13.snide.com That's a bit OTT isn't it, Dave? Why not: 1) UNLOAD TO tabname.unl SELECT *, 0 FROM tabname; 2) DELETE FROM tabname; 3) modify tabname (Table/Alter) and add a SERIAL START 800 on the end 4) LOAD FROM tabname.unl INSERT INTO tabname; 5) modify tabname (Table/Alter) and change the SERIAL column to a SMALLINT This is now a single isql session, not even dbschema needed (and you could of course make it a single .sql script by substituting ALTER TABLE statements for Table/Alter commands). Unfortunately, both these "solutions" make the potentially naive assumption, that the UNLOAD statement will ORDER the rows in the way in which hsc wishes to number them. Since (s)he omitted to tell us what order (s)he needed to number them in, this is somewhat understandable. The fact that (s)he cares about the values in the new inventory_number column would indicate that there is data somewhere which could (should?) be used to UPDATE the rows without all this hassle. (S)He just hasn't told us about it. -- graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.