CODEWITHSUNDEEP
sqlsql-server
Bella
Bella

Reputation: 87

How to make string auto increment

I got 2 different table:

Table1

ID
AK001 
AK001 
AK001

(above should be AK001, AK002, AK003 and so on....)

Table2

LastNum
 1

Table 1: update table1 set ID = xx + RIGHT('....'+()) and so on

Table 2: UPDATE table2 SET LastNum = LastNum + 1 (this works, but when I enter wrong detail when at create new form ,not yet save, its also give me +1)

my question is how to auto increment and update in Table 1 after Table 2 have been updated? Because what im facing is the table1 keep give me the same number when table2 is updated. Please help!

Upvotes: 4

Views: 26379

Answers (2)

Yaroslav
Yaroslav

Reputation: 6534

You should consider using INT as primary key and not a varchar. Then use that field as foreign key.

Here is a detailed answer on why not to use VARCHAR as any type of key.

Keep storing yout store ID as varchar, just add new field on both tables, on the main one make sure to be IDENTITY, it would auto-increment.

There are many opinions on the subject about using VARCHAR as primary key. You should carefully analize the problem you are trying to solve. And ofcourse, maybe you just can't change anything and work with what already you have. On that case you need to use some work around as a trigger as suggested by @sgeddes.

Upvotes: 3

sgeddes
sgeddes

Reputation: 62841

Assuming Table1 should always end with XXX which corresponds to the LastNum field in Table2, sounds like a Trigger would be most efficient.

Which RDBMS are you using?

If MySQL:

CREATE TRIGGER myTrigger BEFORE INSERT ON Table2
  FOR EACH ROW BEGIN
    //Here you should use NEW.Lastnum to UPDATE your Table1
    // update table1 set ID = xx + RIGHT('....'+())
  END;

SQL Server:

CREATE TRIGGER myTrigger 
ON Table2
AFTER INSERT
AS
   //Here you should use INSERTED table (Lastnum column) to UPDATE your Table1
   // update table1 set ID = xx + RIGHT('....'+())
GO

You should be able to lookup other syntaxes that are RDBMS specific.

Good luck.

Upvotes: 1

Related Questions