MySQL if exists update values else insert

Question

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.

You don't need the primary key, any unique index will trigger ON DUPLICATE KEY. You should probably have a unique index on (module_code, lecture_name, user_id) — Barmar
– Barmar, Commented Apr 29, 2017 at 16:14

Barmar · Accepted Answer · 2017-04-29 16:16:24Z

1

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.

edited Apr 29, 2017 at 16:16

Barmar

789k57 gold badges554 silver badges669 bronze badges

answered Apr 29, 2017 at 16:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barmar Over a year ago

I suspect module_code should also be in the unique index.

Collectives™ on Stack Overflow

MySQL if exists update values else insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related