SQL Alter Table Command

Databases
1

In the SQL Alter Table command, is there a way to tell it where to put the column or is it always appended to the end of the table.

karter.SQLExecute ("ALTER TABLE myeventplanner ADD COLUMN lease text")

As with the above code the lease column is put at the end, is there a way to move it up the order.

Thanks

2

Platform?

MS SQL Server can be restructured using drag and drop in SSMS. Sequel Pro will do the same with MySQL. SQLite Expert purports to restructure as well for SQLite (haven’t tested it though).

Maybe this helps… alter table add column at position

3

For SQLite:

http://sqlite.org/lang_altertable.html

4

In a relational sql db columns dont have a “position”
They’re tuples
Usually they are presented in the order of creation but I have seen them in alphabetical order and even a few others (grouped by column type etc)
How you want it presented is a display preference more than anything

5

But aren’t they’re in a repeatable order that we perceive to be position (i.e an SQL dump doesn’t output in a random order each time)?

6

You will have to look at the vendor’s implementation.

But the definition of SQL is, that there is no order of the columns. They’re actually not columns, they’re attributes - like with properties of a class in OOP languages there is no order for them too.

The above is also the reason to never use SELECT * FROM, but always use the names of the attributes like in SELECT rowId, firstname, lastname FROM. And in Xojo I would recommend to always use SomeRecordSet.Field(“lastname”) and never SomeRecordSet.IdxField(2).

7

i only use SomeRecordSet.IdxField(2) if i want to loop through all the fields to create a new record from an existing one.

8

If you mean to duplicate a record, you can do this with Database.SQLExecute:

INSERT INTO table1 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE rowid = 123