INSERT with a COUNT on the same table, in a single query?

Question

I'm trying to get the number of rows in a table, and then insert that number into that same table, all with a single query. I looked at subquery and it throws error since I'm doing it on the same table.

Then I looked at variables and it works but mySQL still throws an error about empty result.

SET @nums := (SELECT COUNT(*) FROM myitems);
INSERT INTO myitems ( `label`, `counted`) VALUES ('blah', @nums);

Can I trust this will be robust? I'm not super expert on SQL statements.

PS: I know about AUTO_INCREMENT which you might think should be utilized here. I simplified my situation to keep the question esy to digest (and hopefully answer).

Answer 1

Why not use INSERT ... SELECT?

INSERT INTO myitems( `label`, `counted`)
SELECT 'blah', COUNT(*) FROM myitems;

I don't know why you would do this but it does answer your question