none
Search all items in table and update another table if not existing sql 2016

    Question

  • Hi all,

    I am hoping someone can help me with this issue.

    It concerns 3 tables in sql 2016...  stockItem,StockLocation, Sublocation.

    What I need to do is check in the stocklocation table which holds amongst others the following fields

    Itemid,Sublocation,Stockquantity.

    Each stock item should have an entry in the stocklocation table, and an entry for each sublocation it is held at. The problem is that this is not the case.

    So what  I need to do is to go through each stock item (there are hundreds of them) and sublocation and if the stock item and the sublocation is not held in the stocklocation table, then add stockitem,sublocation,Stockquantity(=0).

    If the stockitem AND sublocation exist, then do nothing.

    I hope that this makes sense...and that someone has a suggestion solution.

    Thanks a million

    Aidan

    Friday, February 09, 2018 11:37 PM

Answers

  • Hi Aidan,

    As there is no relationship between StockItem and SubLocation table, you need to make a CROSS JOIN between StockItem and SubLocation table to get all possible combinations of item and locations and then do an except between existing entries in StockLocation table and insert them with a '0' quantity value. 

    Here is what you would be writing (change column names as appropriate):

    INSERT INTO StockLocation (Itemid,Sublocation,Stockquantity)
    SELECT diff.ItemId, sl.SubLocation, 0 AS Stockquantity
    FROM
    (SELECT DISTINCT si.ItemId, sl.SubLocation
     FROM StockItem si 
    	CROSS JOIN SubLocation sl 
     EXCEPT
     SELECT DISTINT ItemId, Sublocation
     FROM StockLocation) diff


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    • Marked as answer by aidan1972 Monday, February 12, 2018 3:01 PM
    Saturday, February 10, 2018 12:06 AM
  • can you share table structues / ddl

     
     INSERT INTO StockLocation( Itemid , Sublocation  , Stockquantity) 
     SELECT b.Itemid ,  b.Sublocation , 0 AS Qnty
     FROM 
     (
      SELECT DISTINCT StockItem.Itemid , Sublocation.Sublocation
      FROM StockItem , Sublocation
     ) b 
     LEFT JOIN StockLocation  ON StockItem.Itemid = StockLocation.Itemid AND StockItem.Sublocation = StockLocation.Sublocation
      WHERE StockLocation.Itemid IS NULL  

    
    • Marked as answer by aidan1972 Monday, February 12, 2018 3:01 PM
    Saturday, February 10, 2018 12:16 AM

All replies

  • Hi Aidan,

    As there is no relationship between StockItem and SubLocation table, you need to make a CROSS JOIN between StockItem and SubLocation table to get all possible combinations of item and locations and then do an except between existing entries in StockLocation table and insert them with a '0' quantity value. 

    Here is what you would be writing (change column names as appropriate):

    INSERT INTO StockLocation (Itemid,Sublocation,Stockquantity)
    SELECT diff.ItemId, sl.SubLocation, 0 AS Stockquantity
    FROM
    (SELECT DISTINCT si.ItemId, sl.SubLocation
     FROM StockItem si 
    	CROSS JOIN SubLocation sl 
     EXCEPT
     SELECT DISTINT ItemId, Sublocation
     FROM StockLocation) diff


    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    • Marked as answer by aidan1972 Monday, February 12, 2018 3:01 PM
    Saturday, February 10, 2018 12:06 AM
  • can you share table structues / ddl
    Saturday, February 10, 2018 12:06 AM
  • can you share table structues / ddl

     
     INSERT INTO StockLocation( Itemid , Sublocation  , Stockquantity) 
     SELECT b.Itemid ,  b.Sublocation , 0 AS Qnty
     FROM 
     (
      SELECT DISTINCT StockItem.Itemid , Sublocation.Sublocation
      FROM StockItem , Sublocation
     ) b 
     LEFT JOIN StockLocation  ON StockItem.Itemid = StockLocation.Itemid AND StockItem.Sublocation = StockLocation.Sublocation
      WHERE StockLocation.Itemid IS NULL  

    
    • Marked as answer by aidan1972 Monday, February 12, 2018 3:01 PM
    Saturday, February 10, 2018 12:16 AM
  • please provide:

    1) Queries to CREATE your table(s) including indexes and relations
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.

    Regards,

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, February 10, 2018 2:36 AM
    Moderator
  • Thanks Ashish,

    I could work out from your example.

    Thanks for your time.


    Aidan

    Monday, February 12, 2018 3:02 PM
  • You're welcome Aidan. Glad, I could help.

    HTH,
    Cheers!!
    Ashish
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    Monday, February 12, 2018 6:07 PM
  • >> It concerns 3 tables in sql 2016...   <<

    Why do you expect other people to write the DDL that you're supposed to post for you? We have no idea what the key is for any of the tables, any constraints, data types or references. Are you working some place where they make you program from vague narratives instead of real programming specifications? Did you know that tables model sets of things, so their names have to be either collective or plural nouns?

    Your "sub_location" is probably a design error called "attribute splitting"; this is where you take what should be in one column because it models one attribute, and split it across multiple columns or even multiple tables. But since we have no sample data, how are we supposed to tell?

    >> What I need to do is check in the stock_locations table which holds amongst others the following fields [sic: columns are not fields] here is my guess at what you didn't tell us: <<

    CREATE TABLE Stock_locations
    (sku CHAR(10) NOT NULL, 
     location_htm CHAR(16) NOT NULL
       REFERENCES Locations (location_htm),
     PRIMARY KEY (sku,  location_htm),
     stock_qty INTEGER DEFAULT 0 NOT NULL
      CHECK( stock_qty >= 0);

    >> Each stock item should have an entry in the stock_locations table, and an entry for each sub-location it is held at. The problem is that this is not the case.<<

    This is what happens when you split attributes. Your data gets all screwed up.

    Please read a book on basic data modeling. You're saying that a sublocation is so totally different from a location that it doesn't exist in the same time and space. I do not believe that.  I think what you have is a bad encoding scheme. We have no idea what your data looks like

    >> So what  I need to do is to go through each stock item (there are hundreds of them) and sublocation and if the stock item and the sublocation is not held in the stock_locations table, then add sku, stock_qty =0 ..<<

    >> If the sku AND sublocation [sic] exist, then do nothing.<<

    Again, a location is one kind of thing that should not be split up; you wouldn't have this problem, if you had a correct design. 

    >> I hope that this makes sense...and that someone has a suggestion solution. <<

    It does not. Please read the forum rules and post your request correctly.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, February 12, 2018 9:16 PM