0

I have a database with rows of "parents" and "children". Similar entries, but one entry is generic version of the more specific child. However, I want these entries to match exactly in certain columns.

Here's an example of my database:

| ID |  IsChildOfID |   Food    |   Type        |
| 1  |              |           |   Fruit       |
| 2  |      1       |   Apple   |   Fruit       |
| 3  |      1       |   Pear    |   Vegetable   |
| 4  |      1       |   Banana  |   Vegetable   |
| 5  |              |           |   Vegetable   |
| 6  |      5       |   Lettuce |   Fruit       |
| 7  |      5       |   Celery  |   Vegetable   |
| 8  |      5       |   Cabbage |   Fruit       |

In this example there are 2 parents and 6 children. The value of "type" field is inconstant with some of the children. I want to be able to find any children in the database and replace it with their parent's value in only some of the columns. Is this possible with purely MySQL or do I need do it with php? Thanks.

Jack Cole
  • 1,528
  • 2
  • 19
  • 41

3 Answers3

1

Generally, when you use parent/children relationships in sql, you should make two separate database tables for each. In your case, you should create a database entitled "types" and include a type_id for each element in the child table.

Example

Child table:

| ID |    TYPE_ID   |   Food    |
| 2  |      1       |   Apple   | 
| 3  |      2       |   Pear    |  
| 4  |      2       |   Banana  |   
| 6  |      1       |   Lettuce |  
| 7  |      2       |   Celery  | 
| 8  |      1       |   Cabbage |

Type table:

| ID |        Type          |
| 1  |        Fruit         |
| 2  |       Vegetable      |

You can then reference it by looping through the type table, and using a sql statement like

$types = mysql_query ( 'SELECT * FROM type_table');

WHILE ( $type = mysql_fetch_array ( $types ) )
{
 $sql = 'SELECT * FROM  child_table WHERE TYPE_ID = "' . $type['type'] . '"';
}
christopher
  • 615
  • 5
  • 11
  • Currently my database has around 20,000 entries. The "parents" have a lot of the same information as the children, with a couple exceptions. Would it be faster to split the database in 2 and have the children looked up, then look up their parents? Or is this for purely organizational purposes? – Jack Cole Sep 28 '12 at 21:47
  • It is primarily for organization. You have two different data types, so you should have two different tables for them. But if you have that many entries already and only are trying to maintain them, then you should use some PHP/sql to update it. Dusan's answer might work – christopher Sep 28 '12 at 21:49
1
UPDATE name_of_table SET Type = "Fruit" WHERE IsChildOfID = 1

and 

UPDATE name_of_table SET Type = "Vegetable" WHERE IsChildOfID = 5
  • But if you want to do it dynamicaly please use php or some other language...

Also I would prefer to use 2 tables for this kind of data...

Develoger
  • 3,950
  • 2
  • 24
  • 38
  • 1
    OK since you're the second person to say I need to split the tables in two I'll do it. Much simplier than what I'm trying to ask. – Jack Cole Sep 28 '12 at 21:54
  • 1
    @DusanRadojevic Good advice, I always hate doing self joins on tables, kinda takes the "relation" out of a "relational database" :-) – raidenace Sep 28 '12 at 22:17
  • I decided not to do it actually, it over complicated the whole process splitting everything up. – Jack Cole Sep 29 '12 at 01:02
  • I am repeating this in a lot of my comments, please consider what is good for you on a long term basis... – Develoger Sep 29 '12 at 09:33
0

Similar answer here: UPDATE multiple tables in MySQL using LEFT JOIN

I was going to write this:

UPDATE foods c
  JOIN foods p ON p.id = c.IsChildOfId 
   SET c.type = p.type
 WHERE p.isChildOfId IS NULL

But then upon further reading of the link above, not sure you can reference the target table. Worth a try.

Community
  • 1
  • 1
dispake
  • 3,259
  • 2
  • 19
  • 22