OK, here's a lengthy answer:
I don't use MySQLCC, so I'm assuming that it has a way that you can
enter and run SQL statements.
Say our top 10 songs are:
mysql [test]> SELECT * FROM musicchart ORDER BY chartnumber LIMIT 10;
+-------------+----------------------+--------------------------------+
| ChartNumber | Artist | Title |
+-------------+----------------------+--------------------------------+
| 1 | Led Zepplin | All of My Love |
| 2 | Jeff Foster | Volim Te (I Love You) |
| 3 | Dave Brubeck Quartet | Brotherly Love |
| 4 | U2 | Love Rescue Me |
| 5 | Eurythmics | Miracle Of Love (Live) |
| 6 | Charlie | Lovers |
| 7 | Melissa Etheridge | My Lover |
| 8 | The Call | For Love |
| 9 | Jewel | Love and Affection (Vh1 Duets) |
| 10 | Diana Krall | Peel Me a Grape |
+-------------+----------------------+--------------------------------+
10 rows in set (0.00 sec)
Now we move #9 up to #1 and we have to close up the hole that was
created by the move (there is no longer a #9 song (i.e. we have numbers
7, 8, 10, 11).
mysql [test]> UPDATE `MusicChart`
-> SET `ChartNumber` = 1
-> WHERE `ChartNumber` = 9;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql [test]> UPDATE `MusicChart`
-> SET `Chartnumber` = `ChartNumber` - 1
-> WHERE `ChartNumber` > 9;
Query OK, 118 rows affected (0.01 sec)
Rows matched: 118 Changed: 118 Warnings: 0
We now have:
mysql [test]> select * from musicchart order by chartnumber limit 10;
+-------------+----------------------+--------------------------------+
| ChartNumber | Artist | Title |
+-------------+----------------------+--------------------------------+
| 1 | Jewel | Love and Affection (Vh1 Duets) |
| 1 | Led Zepplin | All of My Love |
| 2 | Jeff Foster | Volim Te (I Love You) |
| 3 | Dave Brubeck Quartet | Brotherly Love |
| 4 | U2 | Love Rescue Me |
| 5 | Eurythmics | Miracle Of Love (Live) |
| 6 | Charlie | Lovers |
| 7 | Melissa Etheridge | My Lover |
| 8 | The Call | For Love |
| 9 | Diana Krall | Peel Me a Grape |
+-------------+----------------------+--------------------------------+
10 rows in set (0.00 sec)
Ooops! we have to number 1's. Now we have to shift the rest of the
songs up one slot.
mysql [test]> UPDATE `MusicChart`
-> SET `ChartNumber` = `ChartNumber` + 1
-> WHERE `Title` != 'Love and Affection (Vh1 Duets)';
Query OK, 126 rows affected (0.02 sec)
Rows matched: 126 Changed: 126 Warnings: 0
mysql [test]> select * from musicchart order by chartnumber limit 10;
+-------------+----------------------+--------------------------------+
| ChartNumber | Artist | Title |
+-------------+----------------------+--------------------------------+
| 1 | Jewel | Love and Affection (Vh1 Duets) |
| 2 | Led Zepplin | All of My Love |
| 3 | Jeff Foster | Volim Te (I Love You) |
| 4 | Dave Brubeck Quartet | Brotherly Love |
| 5 | U2 | Love Rescue Me |
| 6 | Eurythmics | Miracle Of Love (Live) |
| 7 | Charlie | Lovers |
| 8 | Melissa Etheridge | My Lover |
| 9 | The Call | For Love |
| 10 | Diana Krall | Peel Me a Grape |
+-------------+----------------------+--------------------------------+
10 rows in set (0.00 sec)
You may want to be careful about the column used in the last statement.
You will want to use some combination of columns or a unique column to
ensure against duplicates. With my example there is a possibility that
you could have two songs with the same title. I think I would add an
auto_increment column that way I'd only have to know the auto_increment
number to use in the WHERE clause. The last statement would become:
UPDATE `MusicChart`
SET `ChartNumber` = `ChartNumber` + 1
WHERE `Song_ID` = 135;
Where Song_ID is some number assigned by MySQL to the record.
On Sun, 1 Jun 2003 17:23:13 +0100, Daniel Crompton wrote:
> Can anyone advise how we should improve this method of updating our
> database.
>
> We have a Music chart it is a table with 3 columns
>
> Chart Number (ie, 1,2,3,4,5,6,7,8,9,10..)
> Artist (Artists Name)
> Title (Title of Song)
>
> Each week we login using mysql control center to make changes to the
> chart positions
>
> The problems is this is currently a very time consuming manual job
> changing the 'Chart Number' Column
>
> 1
> 2
> 3
> 4
> 5
> etc.
>
> If for example we move song number 5 up to number 1, we then have to
> manually change the
> ' Chart Number' of all the ones below it, i.e. changing the old
> number 1 to a number 2,
> number 2 to a number 3, number 4 to a number 5 etc.
>
> Ideally we need to keep using mysqlcc because it is very simple to
> use, but it is very time consuming.
>
> Any suggestions?
>
>
---
Listserv only address.
Jeff Shapiro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
