0

Is it possible to create auto-increment based on a specific field? For example i have UserId and Status fields, so for each row with same UserId i need to auto-increment its Status, not global.

Kin
  • 4,466
  • 13
  • 54
  • 106
  • 2
    This should be what you are looking for: http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment – dognose Jan 18 '13 at 10:02
  • @dognose it depends on engine, so works not in all situations... – Kin Jan 18 '13 at 10:21

1 Answers1

0

There is three thing that come to mind when I read your question. One was an auto incrementing field which acts as your ID number. Updating a table with data that has no unique ID number. Searching for fields with the same Userid to Status Mt First example is of a creating a table and your AUTO_INCREMENTing number ID:

CREATE TABLE tableNameHere
(
UniqueID int NOT NULL AUTO_INCREMENT,
FirstName varchar(255) NOT NULL,
StatusOrYourColumn int(100) NOT NULL,
PRIMARY KEY (UniqueUD)
)

More on auto incrementation.

You may have already built your table and now want to 'add' additional and or modify your fields using ALTER:

ALTER TABLE tableNameHere StatusOrYourColumn INT AUTO_INCREMENT PRIMARY KEY

But be careful, you don't want to overwrite your settings that you have already set.

Another Thing that came to my mind when reading was where you said Status and Userid where the same. You can find these using the WHERE clause like so:

SELECT * FROM tableName WHERE tableName.Userid = anotherTableOrTableName.Status

Using these queries you can update, remake, alter and query your database table.

bashleigh
  • 8,813
  • 5
  • 29
  • 49
  • you might not understand me - as from yours example for each `FirstName` i need to auto-incriment `StatusOrYourColumn`. So when i adding `Name1` `StatusOrYourColumn` becomes 1, when i adding second time `FirstName` in current row `StatusOrYourColumn` becomes 2. – Kin Jan 18 '13 at 10:21
  • Where I used `Uniqueid`replace with `Status`. Then when using `INSERT` give the field a `NULL` value so that the auto incremation happens. If you want to do it manually use this `SET Status=Status+1` – bashleigh Jan 18 '13 at 10:29