|
|
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. |
|
There is no answer at this time. |
|
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. |
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 Home - Answers FAQ - Terms of Service - Privacy Policy |