CODEWITHSUNDEEP
mysqlsql-order-bysql-insert
user110971
user110971

Reputation: 153

MySQL: insert select in order

I want to insert data into a table in a specific order. This is because I need to give each entry a specific ID. What I am using is a select statement:

select (@i := @i + 1) as id, ...
order by column

The problem I am having is that this does not seem to work. I get the result I want from the select query. However, when I try to insert the data into the table the order by statement is ignored. Is there any way to force the correct order in the insert statement?

What I want is this:

+----+------+-------------+
| id | name | breadcrumbs |
+----+------+-------------+
|  1 | test | 01          |
|  5 | -d   | 01,05       |
|  4 | c    | 04          |
|  6 | e    | 06          |
|  2 | -a   | 06,02       |
|  3 | --b  | 06,02,03    |
+----+------+-------------+

To become this:

+----+------+-------------+
| id | name | breadcrumbs |
+----+------+-------------+
|  1 | test | 01          |
|  2 | -d   | 01,05       |
|  3 | c    | 04          |
|  4 | e    | 06          |
|  5 | -a   | 06,02       |
|  6 | --b  | 06,02,03    |
+----+------+-------------+

In a separate temporary table.

Upvotes: 1

Views: 1828

Answers (2)

P.Salmon
P.Salmon

Reputation: 17615

I would make certain that @i is initalised see select in from clause below

MariaDB [sandbox]> drop table if exists t;
Query OK, 0 rows affected (0.14 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> create table t(id int, name varchar(10), breadcrumbs varchar(100));
Query OK, 0 rows affected (0.18 sec)

MariaDB [sandbox]> insert into t values
    -> (  1 , 'test' , '01'      ),
    -> (  5 , '-d'   , '01,05'   ),
    -> (  4 , 'c'    , '04'      ),
    -> (  6 , 'e'    , '06'      ),
    -> (  2 , '-a'   , '06,02'   ),
    -> (  3 , '--b'  , '06,02,03');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> drop table if exists t1;
Query OK, 0 rows affected (0.13 sec)

MariaDB [sandbox]> create table t1 as
    -> select
    -> @i:=@i+1 id,
    ->  t.name,t.breadcrumbs
    -> from  (select @i:=0) i,
    -> t
    -> order by breadcrumbs;
Query OK, 6 rows affected (0.22 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select * from t1;
+------+------+-------------+
| id   | name | breadcrumbs |
+------+------+-------------+
|    1 | test | 01          |
|    2 | -d   | 01,05       |
|    3 | c    | 04          |
|    4 | e    | 06          |
|    5 | -a   | 06,02       |
|    6 | --b  | 06,02,03    |
+------+------+-------------+
6 rows in set (0.00 sec)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

I want to insert data into a table in a specific order.

There is no internal order to the records in a MySQL database table. Tables are modeled after unordered sets. The only order which exists is the one you apply by using an ORDER BY clause when you query. So moving forward, instead of worrying about the order in which your records are inserted, you should instead make sure that your table has the necessary columns and data to order your result sets the way you want.

Upvotes: 0

Related Questions