Google Answers Logo
View Question
 
Q: MySQL - Update rows that have same daughter records ( No Answer,   3 Comments )
Question  
Subject: MySQL - Update rows that have same daughter records
Category: Computers > Programming
Asked by: mattmclaughlin-ga
List Price: $15.00
Posted: 22 May 2006 10:23 PDT
Expires: 01 Jun 2006 04:11 PDT
Question ID: 731342
Here is what I am trying to do (in MySQL 4.0.25)

Table 1: t_city (Has the population of each city_id)
Fields:  city_id, population

Table 2: t_city_zip (links each city_id to all the zipcodes that are within it)
Fields:  city_id, zipcode

Answering this question involves writing one or more queries that will
do the following:

for every row in t_city where population=0, find if there is
another row in t_city that that has the following criteria:

- It has the exact same number and content (number and zip code values)
of "daughter" rows in t_city_zipcode regardless of their order

- It has a population > 0

When the query finds this "matching" row in t_city then update the "0"
population to that "non-zero' population of the matching row.

Below is an example of what I am looking to do...

t_city: (city_id, population)
Row 1: (1, 25000)
Row 2: (2, 0)
Row 3: (3, 0)

t_city_zipcode: (city_id, zipcode)
Row 1: (1, 21146)
Row 2: (1, 21401)
Row 3: (2, 21401)
Row 4: (2, 21146)
Row 5: (3, 21401)
Row 6: (3, 21402)

Successfully running this query would have the following impact on t_city:

Row 1: Not evaluated because population > 0

Row 2: Set "population" for row 2 of t_city to 25,000 because:

1) population for row 2 is 0
2) it found a matching row in t_city (Row 1) which has the exact same makeup of
zipcode values (despite being in a different order) and that has a
population <> 0

Leaves population for t_city Row 3 unchanged because it has no
matching t_city rows that meet the criteria.
Answer  
There is no answer at this time.

Comments  
Subject: Re: MySQL - Update rows that have same daughter records
From: monicb-ga on 22 May 2006 14:05 PDT
 
Why Row 3: (3, 0) in t_city table should not be updated even there is 
Row 5: (3, 21401) in t_city_zipcode table which is having the same zip
code number of Row 2: (1, 21401) ?
Subject: Re: MySQL - Update rows that have same daughter records
From: mattmclaughlin-ga on 23 May 2006 03:54 PDT
 
I need all the "subrow" area codes to be the same.

Row 1 (1,25000) in t_city links to the following areacode subrows (21146, 21401)
Row 3 (3,0) in t_city links to the following areacode subrows (21401, 21402)

Since [set of (21401,21146)] <>  [set of (21401,21402)] then I should
not update row 3.

Note that row 2 updates because the content of the "subrow" sets is
the same despite being in different order (before sorting)
Subject: Re: MySQL - Update rows that have same daughter records
From: mwarden-ga on 24 May 2006 15:47 PDT
 
You want to perform relational division. This is going to be a huge
pain because you cannot do subqueries in your version of MySQL. Here's
the idea with subqueries:

select distinct A_KEY
from TABLE_A A
where not exists ( 
    select B_KEY
    from TABLE_B B
    where not exists ( 
        select * 
        from TABLE_C C
        where A.A_KEY = C.A_KEY
        and B.B_KEY = C.B_KEY ))

The double negated existential query is needed because there is no
universal quantification (e.g., FORALL) in SQL.

I tried to work out the formulation in left outer joins, but I cannot.
I hope this points you in the right direction. Good luck.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy