Reindexing field in table

Dev Shed Forums Sponsor:

October 11th, 2012, 01:47 AM

TASB

Contributing User

Join Date: Apr 2009

Posts: 155

Time spent in forums: 4 Days 8 h 9 m 7 sec
Reputation Power: 88

Reindexing field in table

Hello.

I have topics table.

Code:

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| topic_ID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| nav_ID      | int(10) unsigned | NO   |     | NULL    |                |
| topic_index | int(10) unsigned | NO   |     | NULL    |                |
| topic_name  | varchar(100)     | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

The topic_index field is used to store sub indexing of the topics which belong to a specific nav_id. Users have the ability move topics up or down in the list.

There is one particular nav_index value where I want the topics to be indexed alphabetically.

Writing a PHP routine to reorder the topic_index field would be simple enough but I would like to achive the task within MySQL.

So in essence if you query the table;

select * from om_topics where nav_id=3 order by topic_name asc;

I wold like to update topic_index = rowNum from the previous select query.

October 11th, 2012, 02:00 AM

TASB

Contributing User

Join Date: Apr 2009

Posts: 155

Time spent in forums: 4 Days 8 h 9 m 7 sec
Reputation Power: 88

Thanks,

I figured it out.

Code:

update
	om_topics t, 
	(select 
		@rownum:=@rownum+1 'rank', 
		om_topics.topic_id 
	from 
		om_topics, 
		(SELECT @rownum:=0) r 
	where 
		nav_ID=3 
	order by 
		topic_name asc) r 
set 
	t.topic_index = r.rank 
where 
	t.topic_ID=r.topic_ID;

Last edited by TASB : October 11th, 2012 at 02:03 AM.

October 12th, 2012, 12:36 PM

rdubya

Registered User

Join Date: Oct 2012

Posts: 4

Time spent in forums: 23 m 22 sec
Reputation Power: 0

Thanks for the help TASB. I think I am getting close but I am still not quite there... any help would be most appreciated:

UPDATE PlayerRankings p,
(SELECT @rownum := @rownum + 1,
@rank := IF(@prev_val!=Score,@rownum,@rank) AS rank,
@prev_val := Score
FROM
p,
(SELECT ((Select @rownum := 0),
(SELECT @rank := 1) ,
(SELECT @prev_val = NULL)) AS ra)
WHERE
idGame = 1
ORDER
BY Score DESC)
SET p.Rank = ra.rank;

Error Code: 1248. Every derived table must have its own alias 0.015 sec

I looked up the error but I don't think I grasp the concept of it with this.

Thanks,
Ryan

October 12th, 2012, 01:02 PM

r937

SQL Consultant

Dev Shed God 43rd Plane (26000 - 26499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,444

Time spent in forums: 3 Months 1 Week 2 Days 14 h 25 m 12 sec
Reputation Power: 4141

Quote:

Originally Posted by rdubya

I looked up the error but I don't think I grasp the concept of it with this.

a derived table is a subquery in the FROM clause

here's a derived table with an alias --

(SELECT @prev_val = NULL) AS ra

here's one without --

(Select @rownum := 0)

Comments on this post

rdubya agrees!

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

October 12th, 2012, 04:02 PM

rdubya

Registered User

Join Date: Oct 2012

Posts: 4

Time spent in forums: 23 m 22 sec
Reputation Power: 0

Hmm.. I tried setting all the tables as derived:::

UPDATE PlayerRankings p,
(SELECT @rownum := @rownum + 1 AS rownumber,
@rank := IF(@prev_val!=Score,@rownum,@rank) AS rank ,
@prev_val := Score AS score
FROM
p,
(SELECT ((Select @rownum := 0 AS test2) ,
(SELECT @rank := 1 AS test) ,
(SELECT @prev_val = NULL AS prevscore)) AS ra)
WHERE
p.Game_idGame_FK = 1
ORDER
BY p.Score DESC)
SET p.Rank = ra.rank;

Still getting the same error.... Am I not setting them properly?

October 12th, 2012, 05:04 PM

TASB

Contributing User

Join Date: Apr 2009

Posts: 155

Time spent in forums: 4 Days 8 h 9 m 7 sec
Reputation Power: 88

Quote:

Originally Posted by rdubya

(SELECT ((Select @rownum := 0 AS test2) ,
(SELECT @rank := 1 AS test) ,
(SELECT @prev_val = NULL AS prevscore)) AS ra)

I think your problem lies here.

Try

Code:

(SELECT 
	@rownum := 0 AS test2,
	@rank := 1 AS test,
	@prev_val = NULL AS prevscore) AS ra;

October 12th, 2012, 07:13 PM

rdubya

Registered User

Join Date: Oct 2012

Posts: 4

Time spent in forums: 23 m 22 sec
Reputation Power: 0

Same error =( thanks for trying though-- any other ideas?

UPDATE PlayerRankings p,
(SELECT @rownum := @rownum + 1 AS rownum,
@rank := IF(@prev_val!=Score,@rownum,@rank) AS rank ,
@prev_val := Score AS score
FROM
p,
(SELECT @rownum := 0 AS test2,
@rank := 1 AS test,
@prev_val = NULL AS prevscore)AS ra
WHERE
p.Game_idGame_FK = 1
ORDER
BY p.Score DESC)
SET p.Rank = ra.rank;

Reindexing field in table

Developer Shed Advertisers and Affiliates