0
384
ViewsMySQL 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.
1 answers
Answer question0
The use of null in double quotes is troubling. So, I think you should write the query as:
INSERT INTO `attendance`(`module_code`, `lecture_name`, `attended`, `total`, `user_id`)
VALUES ('test', 'TEST', 1, 1, 1)
ON DUPLICATE KEY UPDATE attended = attended + 1, total = total + 1 ;
For this to work, you seem to want a unique index on (lecture_name, user_id).
Your data model does seem suspicious because the table is repeating lecture_name on multiple rows. It seems to me that you would want a lectures table and to use lecture_id in this table.