CODEWITHSUNDEEP
pythonmysqlsql-update
Albandry
Albandry

Reputation: 15

Update with inner join and where conditions

I am working on MySQL and created two tables one for students_data and one for Attendance_details

Attendance table has FK of Student Id Here is the Attendance table

AttID, ID, DateTime, Statues

And the students_data table

fname, lname, ID
ABN   , AA , 123

My question:

I want to update the Attendance_details table based on student ID like the query below

sql = " UPDATE Attendance_table INNER JOIN students_data ON Attendance_table.ID=students_data.ID SET Attendance_table.ID=students_data.ID , Statues=%s , DateTime=%s , WHERE students_data.fname = %s"
val=(1,formatted_date,name)
mycursor.execute(sql,val)
mydb.commit()

I got an error

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 'WHERE students_data.fname = 'ABN''

Note: I'm working on face recognition project where all faces have a predefined name and compare it with the students_data so the name ABN has been read from face :)

any help or suggestions!

Upvotes: 0

Views: 347

Answers (2)

Tushar
Tushar

Reputation: 568

If you want to insert data in Attendance table, then avoid UPDATE query. You should use INSERT query and you don't need to JOIN in query.

INSERT INTO Attendance_table (ID,Statues,DateTime) SELECT ID, %s as Statues, %s as DateTime FROM students_data WHERE students_data.fname = %s

Upvotes: 0

Tushar
Tushar

Reputation: 568

You used an extra comma before where 

DateTime=%s , WHERE students_data.fname = %s"

Remove this comma. Your code should be: 

sql = " UPDATE Attendance_table INNER JOIN students_data ON Attendance_table.ID=students_data.ID SET Attendance_table.ID=students_data.ID , Statues=%s , DateTime=%s  WHERE students_data.fname = %s"

Upvotes: 1

Related Questions