MySQL Forums
Forum List  »  Newbie

Trying to Update Table with Rank...
Posted by: Richard Cranney
Date: October 09, 2009 02:49PM

Hello,

I wondered if anyone could help me with a probably simple SQL command. I use DB2 SQL so I've noticed some changes are hitting me for 6 on something I used to do on DB2.

My problem is that I'm creating a mini league table and trying to store the positions of the team that week by terms of RANK.

I'll give a bit of background first about the tables and their structure...

Tablename INS_CONTROL
columns date_week

Tablename REP_TABLE
columns date_week (k), team_code (k), points_this_week, points_last_week, points_total, position_this_week, position_last_week

Example of data...

date_week team_code points_t_w, points_l_w, points_t, position_t_w, position_l_w
200901 AAA 10 0 10 0 0
200901 BBB 20 0 20 0 0
200901 CCC 30 0 30 0 0
200902 AAA 40 10 50 0 0
200902 BBB 10 20 30 0 0
200902 CCC 5 30 35 0 0
200903 AAA 20 40 70 0 0
200903 BBB 15 10 45 0 0
200903 CCC 50 5 85 0 0


What I would like to do is populate the column position_t_w with the current position they were in based on the points_t column for that week.

This data runs weekly and will add a new week in every time, so its only the current week I am wanting to update. I wrote something along the lines of...


DROP TABLE IF EXISTS SESSION_REP_TABLE
;

CREATE TEMPORARY TABLE SESSION_REP_TABLE SELECT * FROM REP_TABLE;
;

SET @rownum :=0
;

UPDATE REP_TABLE I
SET
I.position_this_week = (SELECT
@rownum := @rownum + 1
FROM
SESSION_REP_TABLE J
WHERE
I.team_code = J.team_code
ORDER BY
J.points_total desc)
WHERE
I.date_week = (SELECT
date_week
FROM
INS_CONTROL)

But this seems to put the rank in any old order assigned to any team.

In DB2 I would just use a MERGE statement but I can't seem to find that in MySQL unless i'm missing something.

Any help would be much appreciated. Thanks

Options: ReplyQuote


Subject
Written By
Posted
Trying to Update Table with Rank...
October 09, 2009 02:49PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.