mysql - How do I increment value from a row inserted with matching values?

Question

So I am trying to do this process and want to avoid doing multiple db calls. I want to:

1. Insert values into database
2. After insert, increment a column 'count' based off similar values 'ticket_number'
3. And I want to use a trigger to automate it on insert

What I have so far:

CREATE OR REPLACE TRIGGER ChatAutoIncrement
ON chat
FOR INSERT
BEGIN
    UPDATE chat
    SET
        count = ( ( SELECT u.cnt FROM ( SELECT ticket_id, count(*) as cnt FROM chat GROUP BY ticket_id ) u ) WHERE u.cnt > 1 )
    WHERE
        ticket_id IN
        (
            SELECT u.ticket_id, u.cnt
            FROM
                (
                SELECT ticket_id, count(*) as cnt
                FROM chat
                GROUP BY ticket_id
                ) u
            where u.cnt > 1
            GROUP BY u.ticket_id
        );
END;

This is what Workbench suggested:

CREATE DEFINER = CURRENT_USER TRIGGER `blil_insurance`.`ChatAutoIncrement` AFTER INSERT ON `chat` FOR EACH ROW
BEGIN
    UPDATE chat
    SET
        count =
        (
            SELECT u.cnt
            FROM
                (
                SELECT ticket_id, count(*) as cnt
                FROM chat
                GROUP BY ticket_id
                ) u
            where u.cnt > 1
            GROUP BY u.ticket_id
        )
    WHERE
        ticket_id IN
        (
            SELECT u.ticket_id, u.cnt
            FROM
                (
                SELECT ticket_id, count(*) as cnt
                FROM chat
                GROUP BY ticket_id
                ) u
            where u.cnt > 1
            GROUP BY u.ticket_id
        );
END

The table looks like this:

count	ticket_id	date	id
1	1234	22/01/2021 14:01:05	1
1	2534	22/01/2021 14:03:05	2
2	1234	22/01/2021 14:05:20	3
1	1234	22/01/2021 14:05:20	4

question from:https://stackoverflow.com/questions/65845875/how-do-i-increment-value-from-a-row-inserted-with-matching-values

Answer 1

Waitting for answers