序列列基于另一个字段

Question

I'm working on some legacy code/database, and need to add a field to the database which will record a sequence number related to that column.

LIKE Example table data (current):

ID     Q.No.    Session    File No
1      1010       6          1
2      556        6          2
3      598        7          1
4      789        7          2
5      756        8          1

Answer 1

I think you are asking how to add a column to a table that already exists. If this is correct, then you must use the ALTER TABLE command.

ALTER TABLE table ADD COLUMN column_name column_definition;

In your case, let us assume that your table is named table1 and your sequence numbers are basic integers that are small in size. One might use the following command:

ALTER TABLE table1
ADD COLUMN sequence INT;

Once you create your column, you can use an UPDATE query to add sequence numbers if the value is based in a simple function of other values, or you can use a series of UPDATES to handle records individually.

Because I am not an SQL expert, I tend to keep my SQL simple, and I do most of my processing in PHP. I would write a PHP script to assign sequence numbers to each table row, and then send an UPDATE query handling just that row and repeat until the table is filled.