"Andy" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> 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]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
