Creating a column which increments based on the value of another column

[rod985]

Hi All,

I have a "customers" table with the following columns: "id", "owner_id", "customer" with a lot of rows already in it.

Now, I want to add a column called "owner_customer_id". This column would be a customer ID that's unique to the store owner, but not unique to the table (in other words, "owner_id" PLUS "owner_customer_id" is unique)

 

When I add "owner_customer_id" to the table, all the values are initially set to 0. I would like to properly populate the values *without* bringing PHP into the picture - a pure SQL update. Here's an example of what data should look like afterwords:

 

id, owner_id, owner_customer_id, customer

1, 1, 1, Customer A

2, 1, 2, Customer B

3, 1, 3, Customer C

4, 2, 1, Customer A

5, 2, 2, Customer B

6, 1, 4 Customer D

 

What would be the best approach to accomplish this?  Thanks in advance!

 

- Anthony

[ignace]

What are you trying to accomplish with that design? How can a customer have an owner? And doesn't (id, owner_id) of a customer already imply owner_customer_id?

[rod985]

This is a shared database. Multiple ecommerce stores use the same tables. So for example, when a store owner exports a list of customers, I do not want them seeing the system-level ID, but rather a properly-incremented ID that is specific to the store scope.

[ignace]

Do your store owners share customers? Or should they be separated? I still don't get what you want to achieve with owner_customer_id or do you want to create a concatenation of both the customer_id and owner_id (1,1 => 11)? And why can't they know the system id?

[rod985]

They are completely separate.

Store Owner A has customers

Store Owner B has customers

Store Owner A share absolutely no connection to Store Owner B except that they share a database table on a scaled system.

 

It would be strange for Store Owner A to see the system ID when exporting customers. Say he does an export. He will get back:

1, 1, 1, Customer A

2, 1, 2, Customer B

3, 1, 3, Customer C

6, 1, 4 Customer D

... Naturally, the question will be - why are 4 & 5 missing. The answer, of course, is that 4 & 5 belong to Store Owner B. But that's unacceptable. Store Owner A shouldn't see a skip in Customer IDs just because he shares the database with someone else. Hence, owner_customer_id - an ID that is unique to the individual store owner