35

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 :)

Improve this question

6 Answers 6

Reset to default
80

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

cool. i thought they are no real variables. but i was wrong ^_^ thank u very much!!! i had this problem for like 3 weeks and had everytime to use a seperate scripting language, now i can do it directly via mysql :)
The second one is not working as reference to a variable is not set, so it is always set to 1
@bikashphp Interesting. This worked at the time of posting (I know, because I've used exactly this successfully in the past). Looks like MySQL has changed its implementation of UDVs. If you execute set @position := null; beforehand, it works.
I'd like to use this and also subtract increment a variable ('--') in the same line. However, I'm getting an error regarding 'two columns' used in my select. How can I do both? both increment and subtract one from another variable
Keep in mind that UDV are session based and not statement based. Meaning the UDV value(s) will persist when called from additional queries during the same session. To ensure expected results, it is encouraged to always execute SET @position = NULL; before using it in query. If a UDV is only intended to be used in a single statement, it is safest to execute SET @position = NULL; after issuing the statement as well.
|
5

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.

Improve this answer

1 Comment

thx i already knew how to do all categories, but i wanted to make it "simple" thats why i used where id_category = xxx :) but i have to admit your way is much smarter and better :)
3

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.

Improve this answer

Comments

2

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
Improve this answer

Comments

2

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

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

Comments

0

Hope this will work.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.