How To Change The Order Of Columns In Sql

Robinson Wiscon · Sunday, May 1, 2022

SQL Query to re-lodge the columns

572704

Hello All,

I want to know if information technology is possible to re-social club the columns of a table once the rows has been inserted in all the columns.

And then for e.yard I take initially a table containing 3 columns COL1, COL2 & COL3.

Now after data has been inserted into the table, I want to re-society the columns like COL3, COL2, COl1 or may exist COL2, COL1, COL3 etc keeping the data intact.

Thanks,
Parag

Answers

Hoek Member Posts: xvi,080 Gilded Crown
Hi,

You lot cannot reorder the columns.
You lot would use a view on your tabular array to select columns in a specific order.
```
create or replace view v as select col3, col2, col1 from  your tabular array;
```
edit
See:
http://asktom.oracle.com/pls/asktom/f?p=100:eleven:0::::P11_QUESTION_ID:2171590030349

Edited past: hoek on Nov 23, 2009 7:10 PM
As Hoek has suggested to a higher place there is no option simply to create a view.
But ensure yous also take care of the privileges granted to the user and any table reference you lot may have in the application.

Regards,
Bhushan
Hi,

No, you can not re-club the columns.

If you really, really want to, you can:
(1) add together a new cavalcade (at the finish). Let'south call this col4.
(2) copy information course 1 of the original columns (say col2) to col4
(3) drop col2
(4) rename col4 to col2. The club is now col1, col3, col2.
Echo to movement col1 to the end.

Why would y'all want to do this, though?
And so that you can say "SELECT * ..." without naming the columns? That's (slightly) sloppy programming.
So that you lot tin say "INSERT ..." without naming the columns? That'south very dangerous programming.
Instead of using a star, like:
```
select * from your_table;
```
Just proper noun the order in which you would like the columns returned:
```
select col2,col3,col1 from your_table;
```
(lamentable for saying so, but this is a weird question).
Parag Kalra wrote:

I want to know if it is possible to re-order the columns of a tabular array once the rows has been inserted in all the columns.

So for e.g I have initially a table containing 3 columns COL1, COL2 & COL3.

At present subsequently data has been inserted into the table, I want to re-order the columns like COL3, COL2, COl1 or may exist COL2, COL1, COL3 etc keeping the data intact.

Why? What is your reason for wanting to do this? What practise you want to attain by it? If we understand the actual problem, then nosotros may be able to provide some usable suggestions.

The reason why your request makes very footling sense is that the physical sequence of columns in a row in a datablock, has no impact on you lot as programmer writing code.

Why? Because y'all control the order in which y'all want to select columns. You control the order in which you insert columns. You control the guild in which yous update columns.

SQL allows you the programmer to specify the sequence of columns, and subset of columns, that y'all want to use in your SQL.

Why would you want to change the physical table definition, and rewrite the unabridged tabular array on disk (using very expensive I/O) to reorder the physical column sequence?
Sometimes we take table that have many rows and inserting so many rows take lot of time.

And if later on inserting and then many rows if you realise that the gild of columns in the table you accept created and inserted rows is not what you wanted then droping the tabular array, recreating the table with correct social club of columns and reinserting the rows may take lot of fourth dimension.

Hence wanted a shortcut way of correcting the columns.
Hi,

Parag Kalra wrote:
Sometimes we have table that have many rows and inserting and then many rows take lot of time.

And if after inserting and so many rows if you realise that the lodge of columns in the tabular array you have created and inserted rows is non what you wanted so droping the tabular array, recreating the table with correct order of columns and reinserting the rows may take lot of time.

Hence wanted a shortcut style of correcting the columns.

That's ane of the very good reasons for never INSERTing information without naming the columns.

Re-ordering the columns won't solve the problem; it may brand information technology worse, if people go along to use INSERT statements that relied on the old lodge.

Always name the columns in INSERT statements, for example:
```
INSERT INTO table_x (col1,   col2,   col3)        VALUES	    (i,      20,     333);
```
or
```
INSERT INTO table_x (col1,   col2,   col3)        SELECT	     expr1,  expr1,  expr3        FROM	     ...
```
Parag Kalra wrote:

Hence wanted a shortcut way of correcting the columns.

No shortcuts - but the correct way of explicitly naming the columns to ensure that the sequence of columns lucifer the sequence of values. As Frank has shown.

Also go on in mind that bind variables MUST exist used. This is not-negotiable for skilful performance. Non using bind variables is significantly slower - and non by a mere factor or twice as slow.. thirty times and more slower!! Which means doing a 100000 inserts in a few seconds versus doing a 100000 inserts using a few minutes.