Automatic insertion

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Perfidus
Forum Contributor
Posts: 114
Joined: Sun Nov 02, 2003 9:54 pm

Automatic insertion

Post by Perfidus »

I have a table, with 700 lines.
I would like to introduce a new field called Reference.
This field should contain a number (starting from 5000) and a prefix, like ppm.
Line 1, should contain in the field Reference the following:

ppm5001

And line 700 should be:

ppm5700

How can I do this? Some hints!
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Hints, hints...

In the absence of any good ideas ;), perhaps you could:

1. Create the field as usual.
2. Create a loop (pseudo code here...

Code: Select all

for ($i=1;$i=700;$i++) {
 $insertion = 'ppm'.$i;
 mysql_query("update table set NewFieldname = '$insertion' where NewFieldname is null order by id limit 1");
}
What I mean, loop a query using LIMIT (to update only one record) and order by ID (to get it in the correct order). Using "is null" makes sure you do not try to change a record that is allready changed. Might help (?)
User avatar
Pyrite
Forum Regular
Posts: 769
Joined: Tue Sep 23, 2003 11:07 pm
Location: The Republic of Texas
Contact:

Post by Pyrite »

Yea, that's what I was thinking, but if anyone knows any pure SQL methods, I'd for sure be interested. I've had this dilema before ..
Post Reply