"Andy" <[EMAIL PROTECTED]> wrote in message
> 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.

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]

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to