MySQL incrementing value

Question

Yes: Use a user defined variable:

SET @position := 0; -- Define a variable
INSERT INTO products
SELECT id_product, id_category, name, (@position := @position + 1)
FROM db2.products
WHERE id_category = xxx;

The result of increment to @position is the value used for the insert.

---

Edit:

You can skip the declaration of the variable by handling the initial value in-line:

...
SELECT ..., (@position := ifnull(@position, 0) + 1)
...

This can be particularly handy when executing the query using a driver that does not allow multiple commands (separated by semicolons).

You will need to ORDER BY id_category and use two user variables so you can track the previous category id -

SET @position := 0;
SET @prev_cat := 0;

INSERT INTO products
SELECT id_product, id_category, name, position
FROM (
    SELECT
        id_product,
        id_category,
        name,
        IF(@prev_cat = id_category, @position := @position + 1, @position := 1) AS position,
        @prev_cat := id_category
    FROM db2.products
    ORDER BY id_category ASC, id_product ASC
) AS tmp;

This will allow you to do all categories in one query instead of category by category.

Purely to add to @Bohemians answer - I wanted the counter to reset every so often and this can be done like such:

SELECT *,(@position := IF (@position >= 15,1,@position + 1))

Where 15 is obviously the maximum number before reset.

Try setting a value using a subquery like this

 (SELECT MAX(position) FROM products AS T2)+1

Or

(SELECT MAX(position) FROM products AS T2 WHERE id_category = 'product category')+1