Update join query not working for updating record in joined table

Question

1.00/5 (1 vote)

See more:

Imgur: The magic of the Internet[^]

Imgur: The magic of the Internet[^]

Imgur: The magic of the Internet[^]

What I have tried:

i have tried using all joins right left inner outer full none has worked every time it says 0 rows affected.
should i be using insert query instead of update for this particular work?so that i can store S_ID and C_ID values from StudentCourse table to Attendance table?
i am not sure how to write proper insert join query to get it to work

Posted 9-May-19 13:29pm

Member 12899279

Updated 9-May-19 21:58pm

Add a Solution

Comments

Member 12899279 9-May-19 19:45pm

insert into Attendance
SELECT
StudentCourse.S_ID,
StudentCourse.C_ID
FROM StudentCourse
left JOIN Student on StudentCourse.S_ID = Student.S_ID
left JOIN Course on StudentCourse.C_ID = Course.C_ID

where StudentCourse.S_ID='2' and StudentCourse.C_ID='2'

this aint working either

Member 12899279 9-May-19 20:31pm

Still not able to get what i want now i can select only those rows that match in both tables while i want to updte S_ID,C_ID,Date,Pre_Abs from Attendance table then i want to update it with the values that are selected and showed from StudentCourse table in datagridview in c#

Member 12899279 9-May-19 20:41pm

insert into Attendance(S_ID,C_ID,Pre_Abs)
select a.Pre_Abs,sc.S_ID,sc.C_ID from Attendance a,StudentCourse sc
join Student s on s.S_ID=sc.S_ID
join Course c on c.C_ID=sc.C_ID
where sc.S_ID=2 and sc.C_ID=2
this is also not working

CHill60 10-May-19 4:29am

For future reference, many of us cannot access image sites if we are looking at CodeProject at work - lots of sites are blocked by corporations. There is very rarely any need to use a picture to describe a coding problem. So you are reducing the number of members who can help you.
Just type or paste your code into the question. It has the added advantage that members can copy your code to try and fix it. Personally I never bother even attempting to help these days if someone posts a link in their question

Member 12899279 10-May-19 6:21am

there are two actually four tables Student(S_ID(primary key)),Course(C_ID(Primary key)),StudentCourse((S_ID,C_ID(Foreign keys))Bridge table) and attendance table with (S_ID,C_ID(Foreign keys))
what i am doing is displaying data on datagridview by joining tables and slecting S_ID,S_Name,C_ID and Pre_Abs(Attendance table column)columns from all these tables now i want to insert the info present in datagridview to the attendance table when i click on button..i have done this already with simple insert query to attendance table by using datagrdview.rows[i].cell[2] property
i want to know if there,s any better idea to do this so that i can use JOIN instead of using datagridview property with for loop
for now my attendance table is empty while Student,Course,StudentCourse tables are filled with the data
what i want is to display record(S_ID,C_ID) from studentCourse table and (Pre_Abs) from Attendance table and when i submit the attendance i want it to store Pre_abs record against each S_ID,C_ID in the attendance table
i don't think i can explain it any further

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-05-09T21:58:00

Quote:
should i be using insert query instead of update for this particular work?

This kinda says "I have absolutely no idea what I am doing" and that's probably the real problem here.

UPDATE and INSERT queries are very, very different: one changes only existing records, and the other only adds new rows to a a table. That you think they are interchangeable is worrying - and you need to address that before you go much further.

Regardless, the most probable reason it doesn't work is that your WHERE condition is incorrect: if it doesn't select any rows, then nothing gets changed or supplied to INSERT.
But we don't have any access to your data, so we have no idea what it could (or should) do, and no way to test it.

So start by removing the INSERT code completely, and check what the SELECT part does in isolation - when it correctly returns the rows you want, you can start thinking about doing something else with the data!