Re: SQL Query OT question for the experts :)

The following works in MySQL, but obviously (and unlike your client!) you'll want to do this on a copy of the table first... update londonhotelsallphotos set Number = ( if (@hi != HotelID, @line := 1, @line := @line + 1)), HotelID = (@hi := HotelID) Note that if you run this query more than once, you'll need to reset @line manually in between otherwise the first hotelid will have not have numbers running from 1, but from n+1 where n was the maximum number of the last hotel id. Nick On Friday 17 Oct 2003 9:09 pm, Andrew Brampton wrote: > Hi, > I have a client with a database of around 17k entries. Now due to powers > out of my control the table structure looks like: > > CREATE TABLE londonhotelsallphotos ( > HotelID double default NULL, > active_hotel_photo_Name varchar(255) default NULL, > URL varchar(255) default NULL, > Number varchar(50) default NULL, > Name varchar(255) default NULL > ) TYPE=MyISAM; > > and a few example rows look like: > > (105304,NULL,'http://blah/photos/105304/BAB105304.jpg','1','Cairn > Hotel'); > > (105304,NULL,'http://blah/photos/105304/CAB105304.jpg','2','Cairn > Hotel'); > > (105304,NULL,'http://blah/photos/105304/DAB105304.jpg','3','Cairn > Hotel'); > > (105304,NULL,'http://blah/photos/105304/EAB105304.jpg','4','Cairn > Hotel'); > > However the client has recently updated the database and now all entries > look something like: > > (105304,NULL,'http://blah/photos/105304/AAB105304.jpg',NULL,NULL); > > (105304,NULL,'http://blah/photos/105304/DAB105304.jpg',NULL,NULL); > > (105304,NULL,'http://blah/photos/105304/BAB105304_2.jpg',NULL,NULL); > > (105304,NULL,'http://blah/photos/105304/BAB105304_3.jpg',NULL,NULL); > > Now you will notice that the last 3 fields have changed... The client > wanted to change the URL field, but also changed the Number & Name > fields.... With the current coding it appears they require the Number field > to be set to 1, 2, 3, 4 etc... However as you can see the number field are > all NULL now, this is meaning the rows aren't being shown on the PHP page > due to the way the page was coded.. > > Now what I'm asking is for a SQL Query I can use to re-number all the rows > (17,000 ish). The table has many different HotelIDs in it, with at most 5 > rows with the same ID meaning that the Number field won't be higher than 5. > The URL field I think is always unique for all the rows.... Also I don't > mind that the Name field is left NULL. > > A few example rows would be: > > (105304,NULL,'http://blah/photos/105304/BAB105304.jpg','1','Cairn > Hotel'); > > (105304,NULL,'http://blah/photos/105304/CAB105304.jpg','2','Cairn > Hotel'); > > (105304,NULL,'http://blah/photos/105304/DAB105304.jpg','3','Cairn > Hotel'); > > (105304,NULL,'http://blah/photos/105304/EAB105304.jpg','4','Cairn > Hotel'); > > (105356,NULL,'http://blah/photos/105356/EAB105356.jpg','1','Ramada > Jarvis > Bolton'); > > (105356,NULL,'http://blah/photos/105356/CAB105498.jpg','2','Ramada���<� > ���35\J�G� Jarvis > Bolton'); > > If I can't do this with some quick and dirty SQL, I'll write some PHP to do > the process, but since I'm not being paid to fix this problem, and the > client caused it himself I thought I'll take the easier option of using SQL > before I wrote some code out of kindness... > > Thanks very much > Andrew