Updating a field

Question

I have a random one for you guys: I have a table with 4 fields ProductCode, LocationCode, Primary, StockInLocation

Some productcodes only have 1 entry in the table, others have 2 where 1 should be a primary location and the other shouldn't (Primary is either 1 or 0)

For example:
Productcode      LocationCode      Primary      StockInLocation
ABC                        1                        1                        5
BCD                        2                        1                        3
CDE                        3                        1                        5
CDE                        5                        0                        22
DEF                        4                        1                        7
DEF                        7                        0                       10
EFG                        6                       1                        5

What I want to do is reset the "Primary" field to be 1 where the stockinlocation is the highest or there is only 1 entry in the table and all other primary's should be 0

So far, I have first query as update plocate a set a."Primary" = '0'

Now I need a 2nd query that will correctly set the primary field in accordance with the above rule, resulting in the table below:
Productcode      LocationCode      Primary      StockInLocation
ABC                        1                        1                        5
BCD                        2                        1                        3
CDE                        3                        0                        5
CDE                        5                        1                        22
DEF                        4                        0                        7
DEF                        7                        1                       10
EFG                        6                       1                        5

Unfortunately, it has me stumped as I can't figure out how to update ONLY the lines where there is only 1 entry in the table (eg. Products ABC, BCD, EFG) and the lines with the most stock in them (EG. 2nd line of CDE and 2nd line of DEF). Theories anyone?

Please post a minimal reproducible example that illustrates your problem — Mad Physicist, Nov 21 '19 at 12:51
You can see it in my original post, an example of the table and what I want changing with a full worded description of my needs — Danny Potter, Nov 21 '19 at 12:56
@DannyPotter. You also just completely ignored what I asked. Please see https://stackoverflow.com/help/how-to-ask — Mad Physicist, Nov 21 '19 at 13:08

score 0 · Answer 1 · answered Nov 21 '19 at 13:57

I can't answer in terms of the specific software you're using, but I have a general algorithm which you should be able to translate? I used pandas, and any decent database should have the same types of functionality.

Set up test data:

import pandas as pd
df = pd.DataFrame({
    "ProductCode" : ["ABC","BCD", "CDE", "CDE", "DEF", "DEF", "EFG"],
    "LocationCode" : [1, 2, 3, 5, 4, 7, 6],
    "Primary" : [1, 1, 1, 0, 1, 0, 1],
    "StockInLocation" : [5, 3, 5, 22, 7, 10, 5]})

Define a function that outputs the maximum stock across locations of a given product code:

max_stock = lambda x : df.loc[df["ProductCode"] == x, "StockInLocation"].max()

Set the Primary column to zero:

 df["Primary"] = [0]*len(df)

Loop through a list of unique ProductCode values and set Primary to 1 where the StockInLocation is the maximum for that ProductCode:

for x in df["ProductCode"].unique():
    df.loc[(df["ProductCode"] == x) & (df["StockInLocation"] == max_stock(x)), "Primary"] = 1

Updating a field

1 Answers1