0

I want to reorder the data of a table by a field.

The only way I know is creating a new table and passing all data (except the primary key id) from the old table to the new one ordering by id_temp:

INSERT INTO db1.ticket id_temp, text, answer_timestamp, mileage , distance_unit,
 troubles, error_codes, controls, answer_text, answer_pdf, answer_pdf_link,
 estimated_duration, real_duration, delete_operator_id, request_type_id,
 request_properties_id, category_id, operator_id, language_id, deleted_at,
 created_at, updated_at
SELECT (id_temp, text, answer_timestamp, mileage, distance_unit, troubles,
 error_codes, controls, answer_text, answer_pdf, answer_pdf_link,
 estimated_duration, real_duration, delete_operator_id, request_type_id, 
 request_properties_id, category_id, operator_id, language_id, deleted_at, 
 created_at, updated_at) 
FROM db1.ticket_temp order by id_temp;

However this method looks to me very heavy.

Is it another way to reorder the data in a table without creating a new one?

Michele Della Mea
  • 966
  • 2
  • 16
  • 35
  • 1
    Remove the parentheses from `SELECT (id_temp, ....) FROM` – forpas Aug 31 '20 at 14:03
  • FWIW it is not related to the MySQL version. Using parentheses in the way you were doing would cause the same error on any version of MySQL (or any other implementation of SQL). – Bill Karwin Aug 31 '20 at 14:15
  • You have changed the title of this question to something completely unrelated to the original question. The original question was a duplicate of an old question, and this question is now closed. You should ask a new question. – Bill Karwin Aug 31 '20 at 15:29

1 Answers1

1

The select list should not be surrounded by parenthesis. Additionally, note that tables are intrinsically unordered sets, so inserting with an order by clause is probably not very useful:

INSERT INTO db1.ticket (id_temp, text, answer_timestamp, mileage ,distance_unit ,troubles ,error_codes , controls, answer_text, answer_pdf, answer_pdf_link, estimated_duration, real_duration, delete_operator_id, request_type_id, request_properties_id, category_id, operator_id, language_id, deleted_at, created_at, updated_at)
SELECT id_temp, text, answer_timestamp, mileage ,distance_unit ,troubles ,error_codes , controls, answer_text, answer_pdf, answer_pdf_link, estimated_duration, real_duration, delete_operator_id, request_type_id, request_properties_id, category_id, operator_id, language_id, deleted_at, created_at, updated_at
FROM db1.ticket_temp
Mureinik
  • 297,002
  • 52
  • 306
  • 350