0

I have a MySQL table with parent child relationship. I need to update one field of all child records when I give the parent id. Parent can have unlimited child levels in the table.

My table is like this

ID Name ParentID

1 a

2 b 1

3 c 2

4 d 1

How can I do this?

Thank you

user2990386
  • 65
  • 1
  • 8

1 Answers1

0
select * from Child where parent_id in(select parent_id from Parent)

Above example should work for you, also always mention what you have tried in the question.

better solution Create different table like Parent, Child, grand Child. Parent Id should be present in Child table and Child_id should be there in Grand Child table. This will solve your problem.

rinuthomaz
  • 1,393
  • 2
  • 23
  • 38
  • thanks for your suggestion. But this is not working for me. All my records are in one table. with ParentID column in it. – user2990386 Jun 10 '14 at 07:37
  • So, parent entries(Parent_id) are repeating in your table for each child ? – rinuthomaz Jun 10 '14 at 07:46
  • yes.. my table is like this – user2990386 Jun 10 '14 at 08:07
  • ok.. np.. select * from table where parent_id = 4 is not working ? Can you share the query you are trying. – rinuthomaz Jun 10 '14 at 08:16
  • I need all child records to be listed... for example if I say parent id 1 I need all 2,3,4 listed.. 3 need also need to be listed as 1 is grand parent of 3. like this I have unlimited level of child records – user2990386 Jun 10 '14 at 08:39
  • ID Name ParentID 1 a 0 2 b 1 3 c 2 4 d 1 if you say parent_id=1 you will get only entries 2 & 4. You should query on what is common there. – rinuthomaz Jun 10 '14 at 09:16
  • **better solution** _Create different table like Parent, Child, grand Child. Parent Id should be present in Child table and Child_id should be there in Grand Child table_ This will solve your problem. – rinuthomaz Jun 10 '14 at 09:19