rod985 Posted April 21, 2010 Share Posted April 21, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/199210-creating-a-column-which-increments-based-on-the-value-of-another-column/ Share on other sites More sharing options...
ignace Posted April 21, 2010 Share Posted April 21, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/199210-creating-a-column-which-increments-based-on-the-value-of-another-column/#findComment-1045646 Share on other sites More sharing options...
rod985 Posted April 21, 2010 Author Share Posted April 21, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/199210-creating-a-column-which-increments-based-on-the-value-of-another-column/#findComment-1045784 Share on other sites More sharing options...
ignace Posted April 21, 2010 Share Posted April 21, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/199210-creating-a-column-which-increments-based-on-the-value-of-another-column/#findComment-1045795 Share on other sites More sharing options...
rod985 Posted April 21, 2010 Author Share Posted April 21, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/199210-creating-a-column-which-increments-based-on-the-value-of-another-column/#findComment-1045823 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.