CODEWITHSUNDEEP
sql
DaCard
DaCard

Reputation: 651

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

Upvotes: 1

Views: 39

Answers (1)

Schwern
Schwern

Reputation: 165268

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.

Upvotes: 2

Related Questions