check number row is equal to 10 with same value

I would like to check number of row is equal 10 with same value. I insert the value like that

INSERT test (value) SELECT 'AAA' AS value FROM seq_1_to_10;

and i want to check the number of value is equal to 10 row in sql, but I want in mariadb


DO \$\$ DECLARE cp int; BEGIN SELECT count(*) INTO cpt FROM test WHERE value = 'AAA'; IF cpt != 10 THEN RAISE 'cpt'; END IF; END \$\$;

I am looking for in equivalent the query in mariadb

I have got this error :

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '$$ DECLARE cp int; BEGIN SELECT count(*) INTO cpt FROM test WHERE value = '...' at line 1

Solution

Unless you have disabled the sequence storage engine, your insert will work as is.

Alternatively you can use a recursive cte:

insert test (value)
  with recursive c as (select 1 value union select value+1 from c where value < 10)
  select 'AAA' from c

If you want to check the number of existing rows first, you do need a compound statement, but your syntax is not correct; here's an example, adjust to suit:

BEGIN NOT ATOMIC
  DECLARE c int default 10;
  SET c = c - (select count(*) from test);
  WHILE c > 0 DO
    INSERT test (value) VALUES ('AAA');
    SET c = c - 1;
  END WHILE;
END

fiddle