Setting a ID

[adwaitjoshi]

I have a table that has a few columns out of which are 2 ID and Status. I need to set this ID for all people whose Status is 'Active' this ID will be starting from 1 and incremented as 2, 3, 4, 5, 6, so Max(ID) + 1. Right now the ID field is NULL. Does anyone know how to do this by writing a SQL Statement??

[mitchind]

If you don't have any data in it, just set the field to have the Auto Increment attribute.

That may even auto-fill in your data if you have some in the table already, but that I haven't confirmed.

[adwaitjoshi]

I cant put it to auto increment since this value will be only set where Status = 'Active' in other cases this value will be null

[BerksWebGuy]

Don't know if you need the exact code...but do a sql statement to pull out all active status', then do a loop with a counter in there (counter++; ), then insert that value back into the record.

[adwaitjoshi]

Don't know if you need the exact code...but do a sql statement to pull out all active status', then do a loop with a counter in there (counter++; ), then insert that value back into the record.
Thanks Berks but I know the logic. My question is how to implement it in MySQL with a SQL Statement. i.e. What kind of SQL Statement will I be using and how?

[BerksWebGuy]

I'm not a PHP guru...so there may be a better way...but this should work also...you will need a distinct field doing it this way:


$counter = 0;
$result = mysql_query("SELECT * FROM table WHERE status = 'Active'");
while ( $row = mysql_fetch_array($result) ) {
$distinct = $row['distinct_field'];
$counter++;
$update = mysql_query("UPDATE table SET ID = '$counter' WHERE distinct_field = '$distinct'");
}

[adwaitjoshi]

Berks we are in a different boat. I know how to do it using PHP I want a SQL Statement to do this! There IS a way to write a SINGLE SQL Statement to do this. The end result is not important HOW I do it is important and I need a SQL Statement straight to be executed on the DB.

[mitchind]

Very funny way of posing a question here.
The end result is not important HOW I do it is important

Exactly WHY do you need it done YOUR way?

Do you have data in your table already?

Do you want this to run as a one-shot deal? That is, do you want to fix all existing data?
Or do you want it to run each time a status is turned to 'ACTIVE'?

You will likely get higher quality answers - or at least ones that fit YOUR needs - if you give a LITTLE more detail about your problem..

A suggestion from one of the many VOLUNTEERS here...

[adwaitjoshi]

mitchind,
the reason why i want to do it from SQL and not from PHP is that doing from SQL is tricky and I want to know it for my future use. php may be a option right now but in the future i may like to know how its done from sql. thats the only reason! :)
Adwait

[RTH10260]

mitchind,
the reason why i want to do it from SQL and not from PHP is that doing from SQL is tricky and I want to know it for my future use. php may be a option right now but in the future i may like to know how its done from sql. thats the only reason! :)
AdwaitDoing it my way, and at first it's not a single statement but two:
- create a temporary table with the choice of rows per your selection criteria, and use an AUTONUM field to generate the numbering to your liking.
- in the next step, use the temporary table to update the original table with the generated sequence number.

To do this in once step, I will have to rethink it, likely with a subselect, but I have to check on the MySql version to find out which features are avaiable. I will check on an elderly database I have siting around. I may have used something similat there, but with other functions available thru the db system.