Re: complicated transtable problem

"Andy" <news.letters@gmx.de> wrote in message news:20020418163228.19877.qmail@pb1.pair.com... > Hi there, > > I am still struggling with following problem: > > There are two tables in mysql > 1. city fields: ID, countryid, provinceid, city > containing 2.5 million entries. > 2. province fields: ID, countryid, provinceid, provincename (scratching head) Now, surely that's not normalized... shouldn't it be table Country : id, name table Province : id, country_id, name table City : id, province_id, name > Now there are some cities without a > valid province id in the other table. ... meaning that they link to a non-existent entry? Or to an incorrect entry? Or what? Where did the data come from? Would it be possible to get cleaner data to work with? > I would like to: > 1. delete those province rows (all belonging to > this country) in the province table Not sure I follow here... could you give some sample data? Are you wanting to delete all provinces of a given country which contain no cities? > 2. set the province_id to "" in the city table. This would be simple with a database which supported multi-level SELECTs. However, we will work with what we have. SELECT city.ID FROM city LEFT JOIN province ON city.province_id = province.id WHERE province.provincename IS NULL ... and use the results to build the query... UPDATE city SET province_id=NULL WHERE id IN [your comma-delimited list here]