incrementing integer update

HI

I wondering if there is a simple query to update a new column for my users. I want to issue a non key id number beginning with 500.

So each row would get 501, 502, 503, etc

If without php would be cool


Thank you in advance

 

What programming language would you like it in? If it's for a website you have the choice of PHP, ASP (if your server can run it) or Cold Fusion (if your server can run it).

Or you could set an autoincrement field and insert 500 dummy records, then delete them.

 

What is the best way to do this with php?

 

Can anyone help with this?

 

The best way is to create an autoincrement field, insert 500 records and delete them. The next insert will have the value of 501 in that field.

 

This won't work for me

I have other columns in that table with incrementing values, these will get screwed up. Also I only want to update the rows where the user status column is set to 1, in other words passing over soft deleted records.

Anyone know how to do this?

 

We'd need to see your logic and your table schema to write code for you.

 

Still would like to know if MySQL can perform queries like this

In the meantime I went ahead and wrote a script in php and got the results I needed.. Here is the code if anyone else has use for it.

// database constants

define("DB_SERVER", "");
define("DB_USER", "");
define("DB_PASS", ");
define("DB_NAME", "");

// open database connection

$connection = mysql_connect(DB_SERVER,DB_USER,DB_PASS);
if (!$connection)
{
die("database connection failed: " . mysql_error());
}

// select database
$db_select = mysql_select_db(DB_NAME, $connection);
if (!$db_select)
{
die("database selection failed: " . mysql_error());
}

// perform update query

$i = 1;

$newidnumber= 501; /* starts increment with this value */

$count = 100; /* total records in table goes here */

while ($i < $count) /* loop through all rows /*

{


$result = mysql_query("
UPDATE
accounts_cstm
SET
newidnumber = $newidnumber
WHERE
row_id = $i", $connection);

$i = $i + 1;

$newidnumber = $newidnumber + 1;

if (!$result)
{
die("database query failed: " . mysql_error());
}


} // end loop

 

Your query is doing exactly the same thing as an autonumber field would, it's unconditionally setting the newidnumber field to a number starting with 501 and incrementing for each record.

 

If newidnumber was consecutively numbered already, I guess you could have done something like
SET newidnumber = $newidnumber+500

 

Auto Increments INSERTS

The mySQL way of incrementing number column is 'auto_increment'

create table sometable(
`id` int(11) auto_increment,
primary key (`id`)) AUTO_INCREMENT=500;

when you pass
insert into `sometable` values(null);
and then select * from sometable; then it will be
id
====
500

When you do second insert insert into `sometable` values(null);
it will become
id
====
500
501

and it will keep incrementing when you will be doing inserts.

Custom Auto Increment UPDATE

create table anothertable (
id int(11) auto_increment,
update_id int(11) default '500',
primary key (`id`)
);
insert into anothertable (null, 500);UPDATE sometable SET update_id = update_id WHERE id=1;

 

The table already has an auto incrementing primary key. I don't think you can have more than one auto incrementing column, and the auto incrementing column must in fact be the primary key.

So I managed to assign new incrementing id numbers to the existing records in the table. My next goal is to create a query to retrieve the highest value from this table so I can increment it by 1 with php with the next insert.

I think the best way to do this is SELECT MAX(newidnumber) AS newidnumber FROM user;

The problem is I need to take the max value from 2 joined tables with the same column, because I have my users are really broken into 2 tables.. accounts and contacts

The next record inserted either account or contact needs to be assigned a new non key id incrementing the last max value from both tables by 1

Any suggestion