Can we change the memSQL columns ordinal position in a table

Help
1

Hi,
I am facing a scenario in memSQL which I want to handle.
I have a memSQL table which is getting loaded from csv file.
The column order in the file can be changed from the application which is generating the file.
Meaning if today Col1 is the first in the file, tomorrow it can be 3rd column and so on.

I want to write a pre process step (SP etc), which can check the column order from the file and compare with the table column order. If there is change in order, it can change the column ordinal position in the table as coming in the file before loading, so that there wont be any manual intervention.

Is it possible or a recommended idea to change the ORDINAL_POSITION of the columns in information_schema.columns table to solve this problem?

update information_schema.columns set ORDINAL_POSITION=2 where table_schema=‘schema1’ and table_name=‘test_table_2’ and column_name=‘DIVISION’ and ORDINAL_POSITION=1;

PS: I am using memSQL v6.8.12

Please suggest.

Thanks
Manas

1 Like
2

You can use ALTER TABLE … MODIFY. SingleStoreDB Cloud · SingleStore Documentation.
E.g.

memsql> create table t(a int, b int, c int);
Query OK, 0 rows affected (0.07 sec)

memsql> insert t values(1,2,3);
Query OK, 1 row affected (0.04 sec)

memsql> select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |    3 |
+------+------+------+
1 row in set (0.07 sec)

memsql> alter table t modify c int after a;
Query OK, 1 row affected (0.76 sec)
Records: 1  Duplicates: 0  Warnings: 0

memsql> select * from t;
+------+------+------+
| a    | c    | b    |
+------+------+------+
|    1 |    3 |    2 |
+------+------+------+

Don’t try to directly update information_schema. That’s not recommended and I’m not sure it’s possible.

1 Like
3

And LOAD DATA has an option to specify which columns of the input file to load and in which order. That might be a solution too. See “Example 1” under

4

Thanks Hanson! I will work on modify option to alter the column order.