sunkist
Newbie
Joined: 26 Nov 2007
Posts: 1
|
 Posted: Mon Nov 26, 2007 4:13 am Post subject: Problem with SELECT INTO CACHED workaround |
 |
|
Hi,
I read about the SELECT INTO CACHED workaround for the SELECT INTO problem.
However, the new problem is that this workaround creates a new table where i cant edit the entries. I can still add new columns to it in the design table view though. Anyway to unlock the newly created table? |
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Tue Nov 27, 2007 11:29 am Post subject: |
|
|
sunkist:
You asked about being able to "edit the entries" in a table you have created with a SELECT INTO CACHED statement.
Just for others reading this, I will assume, you have 'created' a new table . . . let us assume for example . . . you have a table named "MY_TABLE" and you want to create a 'new' table named "MY_TABLE_NEW" as a subset of it ( based on the table field "DATE_FIELD" ) . . . so . . . from the Menu: Tools -> SQL... -> you then entered: | Code: | | SELECT * INTO CACHED "MY_TABLE_NEW" FROM "MY_TABLE" WHERE "DATE_FIELD" >= '2007-02-28' AND "DATE_FIELD" <= '2007-07-15' |
Well . . . the above will create your table, BUT, it does NOT yet have a PRIMARY KEY . . . therefore, within BASE, you can 'read' it, but not Modify the data.
So, in order to create the PRIMARY KEY ( must be 'UNIQUE' for each record ) . . . according to HSQL documentation at: http://www.hsqldb.org/doc/guide/ch09.html#alter_table-section
| Quote: | ALTER TABLE <tablename> ADD [CONSTRAINT <constraintname>] PRIMARY KEY (<column list>);
Adds a primary key constraint to the table, using the same constraint syntax as when the primary key is specified in a table definition. |
So, with the above example, from the Menu: Tools -> SQL... -> you then might enter: | Code: | | ALTER TABLE "MY_TABLE_NEW" ADD PRIMARY KEY ("DATE_FIELD", "TIME_FIELD"); |
This will create your designated PRIMARY KEY on fields DATE_FIELD and TIME_FIELD, so, you will be able to "Edit" the entries ( values ) of your newly created table ( of course change the field names BETWEEN PARENTHESES, to reflect YOUR table requirements ).
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved. |
|
