Ackres Ackres - 4 months ago 9
MySQL Question

When i update many to many relationship all records get the same value

Hi I want to make a update on my database using jdbc.

In my database a student can get more than one lesson and a lesson can be get from more than one student.

When I am trying to change a student's lesson record which have more than one lesson.

It makes all lesson's the same value.

I just want to edit a specific lesson.

At the same time I am updating their name,no,class and lesson.

For example:

Jack---English

Jack---Calculus

Jack---Java

I just want to change Java to History but the result is:

Jack---History

Jack---History

Jack---History

I have 3 tables.

-Lesson-

Lid(PK), Number, Code, Lesson_name

-Student-

Sid(PK), Cid(FK), Name, No

-Student_lesson-

Sid(FK), Lid(FK)

My code

result = st.executeQuery("select lid from lesson where lesson_name='"+lesson_name+"'");
if(result.next()){
int lidnum = result.getInt(1);
st.executeUpdate("update student_lesson set lid = '"+lidnum+"' where sid = (select sid from student where no = '"+no+"')");
}

Answer

You need to provide the lesson id as well:

st.executeUpdate("update student_lesson set lid = '"+lidnum+"' 
where sid = (select sid from student where no = '"+no+"')
and lid = whatever_the_lid_of_Java_is");