1

I have four tables by the name candidate, program_type, program_of_interest and desired_intake. The table candidate has a many to one relationship with the rest of the three tables.

The candidate table looks like this: enter image description here

The program_type table: enter image description here

The program_of_interest table: enter image description here

The desired_intake table: enter image description here

I am trying to replace the program_type,program_of_interest,desired_intake id's in the candidate table with the corresponding values in their respective tables. I am able to select the values as needed with help from this thread SQL Replace multiple variables from another table in query result. Here is my solution to select:

SELECT 
     c.id, 
     p.value as 'Program type', 
     p1.value as 'Program of interest',
     d.value as 'Desired intake'
FROM candidate c
JOIN program_type p on p.id = c.program_type
JOIN program_of_interest p1 on p1.id = c.program_of_interest
JOIN desired_intake d on d.id = c.desired_intake

My question is how do I replace the ids in the candidate table with their respective values?

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
utkarsh2k2
  • 1,046
  • 3
  • 19
  • 42

1 Answers1

1

You could use an update basend on join as 

  Update candidate c
  INNER JOIN program_type p on p.id = c.program_type
  INNER JOIN program_of_interest p1 on p1.id = c.program_of_interest
  INNER JOIN desired_intake d on d.id = c.desired_intake
    set c.program_type = p.value,
        c.program_of_interest = p1.value,
        c.desired_intake  = d.value

but seems strange you want update eg: program_type with the value (c.program_type = p.value) and you are using program_type for join ( p.id = c.program_type)

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Hi thanks for the quick response. However the query returns false – utkarsh2k2 Aug 07 '17 at 17:17
  • here is the error `MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN program_type p on p.id = c.program_type JOIN program_of_interest p1 on p' at line 5` – utkarsh2k2 Aug 07 '17 at 17:37
  • I tried the updated query but gives error `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'intake = d.value' at line 7` – utkarsh2k2 Aug 07 '17 at 17:54
  • Itried again, new error `#1054 - Unknown column 'c.program_type' in 'field list'` – utkarsh2k2 Aug 07 '17 at 17:56
  • Tried the new answer, getting new error `#1452 - Cannot add or update a child row: a foreign key constraint fails (`migrationproject`.`candidate`, CONSTRAINT `FK_C8B28E441CBFA1E4` FOREIGN KEY (`program_of_interest`) REFERENCES `program_of_interest` (`id`))` – utkarsh2k2 Aug 07 '17 at 17:59
  • You can't change the foreign key .. the you can't update the values you need .. (you should remove the constrains ... perform the update and then reassign the constrains – ScaisEdge Aug 07 '17 at 18:03