Can you please tell me how to re-order the field positions in a table without having to re-create the table?

It seems it is not able to be done using SQLAnywhere 12.

asked 09 Apr '12, 04:04

Kongthap's gravatar image

Kongthap
110246
accept rate: 0%

edited 10 Apr '12, 17:50

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638


Sorry. But it is not possible.

permanent link

answered 09 Apr '12, 04:09

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.5k213460
accept rate: 15%

edited 09 Apr '12, 16:04

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371108

Is it able to select fields from old_table (as corrected field order) and insert to new table (as creation of new table)

Thanks

(09 Apr '12, 04:54) Kongthap
1

INSERT INTO TABLE A2 with auto name SELECT * from A1 ;

(09 Apr '12, 08:56) Thomas Dueme...
Replies hidden

One might add that the very helpful "WITH AUTO NAME" clause is a SQL Anywhere vendor extension for INSERT ... SELECT, cf. the docs...

(11 Apr '12, 06:47) Volker Barth

Why would you like to re-order the column order?

There are some advanced technical reasons to do so (PKs should be among the first columns, variable-length or "wide" columns should be put at the end, more on this here...) for optimal performance, but in general the column order is rather irrelevant from the database system's point of view - IMHO it's usually more a human need to order columns "logically".

When you code a SELECT statement, you can obviously use whatever column order you like...

But otherwise, Thomas is absolutely right: There is no way to re-arrange the column order of an existing table (though this has beed discussed in the newsgroups in the past several times). So you will have to create a new table with the desired column order.


FWIW: Here's a link to a verrrry long discussion from 2006 on that topic in the "Product Futures newsgroup"...

permanent link

answered 09 Apr '12, 12:33

Volker%20Barth's gravatar image

Volker Barth
29.6k294444650
accept rate: 32%

edited 09 Apr '12, 15:10

2

Historically speaking, it might have been easier for Engineering to implement column reordering, perhaps as part of REORGANIZE TABLE, rather than endure endless discussions year after year... i.e., do it, rather than talk about not doing it :)

(09 Apr '12, 17:19) Breck Carter
Replies hidden

@Breck: Is that a subtle "product-suggestion-reminder"?

(11 Apr '12, 03:24) Volker Barth

Depending on the version of SQL Anywhere the Create Table / Insert / and Select can be combined into one step.

However to answer your question simply, yes.

lets say you have table a1 with the old column order of a,d,c,g,b,e,f

and you have newly created table a2 with desired column order of a,b,c,d,e,f,g

You would perform the following:

INSERT INTO TABLE A2 ( a,b,c,d,e,f,g ) SELECT a,b,c,d,e,f,g from A1 ;

permanent link

answered 09 Apr '12, 06:14

MetaHertz's gravatar image

MetaHertz
76337
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×406
×101
×74
×9

question asked: 09 Apr '12, 04:04

question was seen: 2,752 times

last updated: 11 Apr '12, 06:47