insert serial number column based on other column

1

I have a table having 3 columns CANDIDATE_ID, SERIAL_NUMBER, and created_by_user_id. My requirement is that if I have to insert serial_number as per created_by_user_id. eg. If I have 2 created_by_user_id say 100 and 101. If I am inserting a candidate_id = 1 and created_by_user_id = 100 then SERIAL_NUMBER should be inserted as 1. If again I insert a candidate_id whose created_by_user_id = 100 then SERIAL_NUMBER should be inserted as 2, but if created_by_user_id = 101 then SERIAL_NUMBER corresponding to that should be inserted as 1. My table name is candidate and I am using MySQL.

I have tried using triggers but it is not working properly as it is inserting NULL every time for SERIAL_NUMBER.

Please find the below code I have used and please help me in writing the correct code. Also, If you can write and provide the complete code that would be of great help.

DELIMITER $$ CREATE TRIGGER trig_SerialNumber BEFORE INSERT ON candidate FOR EACH ROW BEGIN DECLARE srNo INT ; IF Old.SERIAL_NUMBER IS NULL THEN SET New.SERIAL_NUMBER = 1 ; ELSE select (MAX(SERIAL_NUMBER) + 1) INTO srNo from candidate where Created_by_User_Id = NEW.Created_by_User_Id; SET New.SERIAL_NUMBER = srNo; END IF; END $$ DELIMITER ;

this is my table after a few inserts

candidate_id SERIAL_NUMBER created_by_user_id 1 NULL 101 2 NULL 102 3 NULL 101
2

If you add the language you need help with to the title of the thread there is more chance of people helping you

Firstly, welcome to the forums.

While we are primarily here to help people with their Free Code Camp progress, we are open to people on other paths, too.

With your current questions, we don’t have enough context to know what you already know or don’t know, so it is impossible to guide you without just telling you the answer (which we won’t do).

It is pretty typical on here for people to share a codepen / repl.it / jsfiddle example of what they have tried so that anyone helping has more of an idea of what help is actually helpful.

Please provide some example of what you’ve tried and I’m sure you’ll get more help.

Happy coding :slight_smile:

I’ve edited your post for readability. When you enter a code block into a forum post, please precede it with a separate line of three backticks and follow it with a separate line of three backticks to make it easier to read.

You can also use the “preformatted text” tool in the editor (</>) to add backticks around text.

Pre-formatted-text
Pre-formatted-text1356×380 401 KB

See this post to find the backtick on your keyboard.
Note: Backticks (`) are not single quotes (’).