sql

Create a new column based on duplicates of another column


I am new to SQL. Assume there is a table with few columns e.g., There are duplicates in id column.

id brand
1 100
1 200
2 100
2 200
3 100
4 100

I would like to create a new column idx based on id and brand columns. E.g.,

id brand idx
1 100 101
1 200 201
2 100 102
2 200 202
3 100 103
4 100 104

So I can use idx to be my primary key in somewhere else. Any suggestions? many thanks


Solution

  • Exactly how you do this depends on the database, but most will be like this.

    -- Add the new column.
    -- It can't be the primary key yet because it will contain nulls.
    alter table brands add column idx integer;
    
    -- Set its value.
    update brands set idx = id + brand;
    
    -- Make it the primary key.
    alter table brands add primary key(idx);
    

    Demonstration in PostgreSQL.


    However, now you have to remember to set that primary key manually when you insert. You could add a default id + brand, but it's entirely possible you will have conflicts. For example, if you have id=10, brand=190 and id=20, brand=180 both will have an idx of 200.

    If you just want a unique primary key, it's better to use an auto-incrementing primary key.

    alter table brands add column idx serial primary key;
    

    Demonstration in PostgreSQL.

    That will add the new column and populate it with a simple automatically incrementing integer. All subsequent inserts will automatically add the next number.

    serial is PostgreSQL specific, other databases will do that slightly differently such as auto_increment in MySQL or identity.