CODEWITHSUNDEEP
mysqlsqlphpmyadminsql-updatesql-order-by
ocean800
ocean800

Reputation: 3737

SQL - UPDATE TABLE and ORDER BY?

So I have an unordered table movement that has columns timestamp,x,and y. I want to order by timestamp, and change and save the table to have the all rows ordered by timestamp.

I wanted to use UPDATE TABLE but I'm unsure on how the query would look... for example, I have this: 

UPDATE movement 
SET ????? 
ORDER BY timestamp;

I don't want to set anything, I just want to change the order of the table. I need to do this for another query that I'm going to write, but the fact is that this table is very large and I need to break up things in steps so that the performance isn't terrible later. So how would I do this? I found this SO post that addressed a similar question, but I was wondering if there was another way to do it rather than use another table, because as I said, this table is very large(millions of rows) and recopying the table would take a long time.

Upvotes: 4

Views: 4105

Answers (4)

scxs
scxs

Reputation: 11

It is actually possible. This is in MySQL format... Update is for editing already existing information. If you want to make more direct changes, use ALTER or MODIFY according to syntax.

ALTER TABLE movement 
ORDER BY timestamp;

Upvotes: 1

Mureinik
Mureinik

Reputation: 312219

Relational database tables represent unordered sets. There is no syntax for sorting a table, simply because there is no such concept as the order of rows in a table. When you issue a query without an explicit order by clause, the database may return the rows to you in any order it may see fit, which might be influenced by the order they were inserted and written to disk, their presence in some memory cache, indexes, or a host of other implementation details.

If you want to query the table's rows sorted by their timestamp, just explicitly state it in the order by clause:

SELECT   *
FROM     `movement`
ORDER BY `timestamp`

Upvotes: 1

Digital Chris
Digital Chris

Reputation: 6202

Tables don't inherently have an order; you don't have to update them into any particular order.

What you do is choose the order of what you SELECT from the table. Then it can be in any order you choose!

Example:

SELECT * FROM movement  
ORDER BY timestamp;

But then somewhere else maybe you want to:

SELECT * FROM movement  
ORDER BY timestamp DESCENDING;

Upvotes: 1

Rahul
Rahul

Reputation: 77926

You can't use ORDER BY in UPDATE statement. ORDER BY should be used with SELECT statement only. Again, there is no need of having the records stored in particular order cause you can just display the records in particular order with a SELECT statement like

select * from movement 
order by timestamp

Upvotes: 1

Related Questions