Member Avatar for tangosdad

I've got two tables - members and sponsor, their simplified structure below.

members:
id_num
program_id
sponsor_program_id

sponsor:
id_num
sponsor

Some of the members in members table are sponsors. I need to update the field sponsor_program_id in all records in members table with their sponsors program_id field. Sponsor table tells me who their sponsor is, i.e. gives me their id_num back in members. So the contents of program_id of the sponsor goes into the sponsor_program_id of the person they sponsored.

I'm very new to this and I am lost! Can someone help?

  • 3 Contributors
  • 3 Replies
  • 83 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by tangosdad
Member Avatar for nav33n 
update members set members.sponsor_program_id=sponser.program_id where sponser.id_num=members.id_num;

This will update the members by setting sponser_program_id to the program_id of sponser when id_num of sponser is equal to id_num of members.

Member Avatar for Jake Kittell

Perhaps you could give an example to more clearly illustrate what you are trying to do?
Jake

Member Avatar for tangosdad

Here is some simplified sample data, maybe this will help.

members:
id_num = 1
program_id = 123
sponsor_program_id = 92

extra:
id_num = 1
sponsor = 0

members:
id_num = 2
program_id = 545
sponsor_program_id =

extra:
id_num = 2
sponsor = 1

Ok, so here are two members. id_num 1 & 2. id_num 1 is id_num 2's sponsor based on record in extra table. I need an sql statement that will update id_num 2's sponsor_program_id with id_num 1's program_id of 123

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.