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.

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

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

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.