CODEWITHSUNDEEP
mysqldatabasejoomlaphpmyadmin
Royce
Royce

Reputation: 189

Altering Table Primary Key ID to be in order of Date Created

I will do my best to outline my problem and question.

Problem:

I exported my articles from MySQL database but did not keep the original Primary Key ID. I transferred it to another database and it became jumbled. The problem is, the Created Date column is not in order according to the primary key ID from the table_contents. Some other info. is the PK is auto-incrementing. See picture below:

Table sorted by ID

As you can see, the created date is not in chronological order at all. Now when I sort the table by created date:

Table sorted by Created Date

The ID's are also not in order.

Possible Solution:

I could manually re-edit every single item in the table, but that would take a long time.

My Thinking:

I am a beginner in SQL, but what kind of SQL query could I write to do what I want? What I was thinking that I would have to create a copy of the table_contents, merge the original, and the copy one together, and then alter the ID's in order by created date. I'm not sure how to write the query to be honest.

My Question:

How would I write a query to alter the primary key IDs column to be ascending in chronological order by the content created date column?

Closure:

If I'm unclear or this is not possible please tell me. Thank you!

Edit: So from the feedback so far I tried writing a query:

ALTER TABLE skwx_content;
SELECT @row_num := @row_num + 1 as newid, skwx_content.id from skwx_content    , (select @row_num := 0) x order by skwx_content.created asc
ALTER TABLE skwx_content DROP PRIMARY KEY;
ADD COLUMN newid INT(10) NOT NULL AUTO_INCREMENT FIRST ORDER BY   skwx_content.created ASC,
ADD PRIMARY KEY (newid);
ALTER TABLE skwx_content CHANGE newid id INT(10);

With what I tried I get error: puu.sh/ohBAC/c3a8b12340.png

Upvotes: 3

Views: 1907

Answers (2)

Dylan Su
Dylan Su

Reputation: 6065

Use a update and order by with a user variable will do.

set @max_id = (select max(id) from mytable);
set @id = @max_id;
update mytable set id = (@id := @id + 1) order by created;
update mytable set id = id - @max_id;

After above SQLs executed, the id will start from 1.

Upvotes: 4

Ramin
Ramin

Reputation: 207

Although the primary key does not need to be in order with any other column as you can query your data and sort it in anyways you want but still if you want to do it you can use this query to get a row number column based on created date

select @row_num := @row_num + 1 as RowNumber, t.[PrimaryKeyColumn] from [tablename] t, (select @row_num := 0) x order by t.CreatedDate asc

You can insert values from RowNumber column into a newly created column in your table by joining this query with your table based on the primary key and then you can make this newly column primary key and remove the old primary key column.

Upvotes: 0

Related Questions