MySQL incrementing value

MySQL incrementing value

Is there a way to make a value increment with every insert if having multiple inserts? (I dont speak of the primary key that autoincrements)

Lets say I have a structure like this:

|ID_PRODUCT|ID_CATEGORY|NAME|POSITION|

So I have individual product ids, each produt belongs to a category and has a different position in this category. I want to do something like this:

INSERT INTO products
( SELECT id_product, id_category, name, MY_POSITION++
  FROM db2.products WHERE id_category = xxx )

So there should be a variable MY_POSITION that starts with 1 and increments every insert.

It would be really easy to do this all just with a scripting-language like php or python, but I want to get better with SQL :)

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

For those who are looking for example of update query, here it is:

SET @i := 0;
UPDATE products SET id = (@i := @i + 1);

Hope this will work.

update <tbl_name> set <column_name>=<column_name>+1 where <unique_column/s>='1'";

ReferenceURL : https://stackoverflow.com/questions/9631096/mysql-incrementing-value