CODEWITHSUNDEEP
phpmysql
tmartin314
tmartin314

Reputation: 4171

Updating field from the name in another Table

I have these two tables:

tbl_courses
id  user_id  course_name
3   5        First Course
4   5        Second Course


tbl_notes
id  user_id  course_id
1   5        3
2   5        4

I am trying to write a simple merge script that is receiving an updated course title: course_name

For example, the user changed First Course to be Second Course and has requested that the all the notes with this new title be merged into this one.

So I am trying to find a way to update all the entries in tbl_notes with course_id from First Course to match the id of Second Course

I am sending $user_id, $course_id, and $course_name as parameters

Upvotes: 0

Views: 45

Answers (2)

Justin
Justin

Reputation: 9724

If I correct understand there is query what you need:

SQLFiddleexample

    UPDATE `tbl_notes`
    LEFT JOIN `tbl_courses` 
      ON `tbl_courses`.`user_id`  =`tbl_notes`.`user_id`
        AND `tbl_courses`.id  = `tbl_notes`.`course_id`
    SET `tbl_notes`.`course_id` = 4
    WHERE `tbl_courses`.`course_name` ='First Course'
      AND `tbl_courses`.`user_id` = 5

With PHP code SQL query:

$sql = "UPDATE `tbl_notes`
LEFT JOIN `tbl_courses` 
  ON `tbl_courses`.`user_id`  =`tbl_notes`.`user_id`
    AND `tbl_courses`.id  = `tbl_notes`.`course_id`
SET `tbl_notes`.`course_id` = $course_id
WHERE `tbl_courses`.`course_name` ='First Course'
AND `tbl_courses`.`user_id` =$user_id ";

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

update tbl_notes set course_id=(select id  
from tbl_courses where course_name='First Course' limit 1)

Upvotes: 0

Related Questions