samedi 29 avril 2017

MySQL if exists update values else insert

I am creating an application that stores a students attendance.

I am creating the database that will store this attendance and I am stuck on the mySQL query.

I want the query to search for the 'lecture name' and the 'user_id' (foreign key from other database) and if that exists increment the students attendance to the lecture (attended++ and total++)

So far I have this:

    INSERT INTO `attendance`(`attendance_id`, `module_code`, `lecture_name`, 
`attended`, `total`, `user_id`) VALUES ("null","test","TEST",1,1,1)
   ON DUPLICATE KEY UPDATE 
 attended = attended+1,total = total+1                                                                                              

This will insert a new row but for the ON DUPLICATE, there is no way to pass the primary key in to check for a 'duplicate'

I was wondering if there is another way to do this in MySQL other than ON DUPLICATE KEY, or if anyone can help me out.

Here is a screenshot of the database. For example see the 'Operations Research' was created when it should of incrememented the values. This is because I have no way of adding the primary key into the SQL query.

I would appreciate any help towards this, Thanks.

screenshot of the database

Aucun commentaire:

Enregistrer un commentaire