Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 4 of 4

Thread: SQL Query String

  1. #1
    DotNetDevelopments is offline Senior Member
    Join Date
    Jul 2008
    Location
    Harlow / Essex / UK
    Posts
    619

    Default SQL Query String

    I have a temp table on my DB called CSV_Import that I import XLS files into, this table has 2 rows, SKU and EAN.

    I am trying to build a query needs to reference the SKU from the CSV_Import table, then find the SKU in either the Product or ProductVariant table and update the DownloadLocation row in ProductVariant (where we place our EAN's).

    I am struggling when a product has many variants and many different SKU's as none of the fields in the SKU row on the table Product have an entry. What happens then is that the SKUSuffix row on the ProductVariant table will have the entry that needs to be queried.

    This the SQL Query that I have at the moment....

    UPDATE ProductVariant
    SET DownloadLocation = CSV_Import.ean
    FROM ProductVariant AS ProductVariant
    INNER JOIN Product AS Product ON ProductVariant.ProductID=Product.ProductID
    INNER JOIN CSV_Import ON Product.SKU = CSV_Import.SKU

    Can anyone offer some help?
    Last edited by DotNetDevelopments; 04-29-2013 at 07:20 AM.
    =====
    Version (Code/DB): AspDotNetStorefront MSx 9.1.0.1/9.1.0.0
    Execution Mode: 64 Bit
    Dot Net Developments - E-commerce By Experience

  2. #2
    jsimacek is offline Senior Member
    Join Date
    Dec 2008
    Location
    Phoenix, AZ
    Posts
    373

    Default

    So if your Product SKU is empty and Variant SKUSuffix is used, shouldn't you join on the SKUSuffix instead of SKU in the last WHERE clause?
    Jan Simacek - Compunix, LLC
    AspDotNetStorefront trusted Devnet Partner and Reseller since 2005

    AspDotNetStorefront Mods and Add-Ons at http://www.ecommercecartmods.com/
    - Searching, Filtering and Sorting (like cSearch, Dealer Locator, Price Ranges, Blog)
    - Reports (like Cart Abandonment and Net Sales)
    - Customer Experience (like Question/Answers)
    - Site and Data Management (like Entity Product Mapper, Bulk Updaters, Make/Model/Year filters)

  3. #3
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    Yes. I think 2 separate statements are required.
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!

  4. #4
    DotNetDevelopments is offline Senior Member
    Join Date
    Jul 2008
    Location
    Harlow / Essex / UK
    Posts
    619

    Default

    Thanks for the reply... ended up using two statements as suggested.
    =====
    Version (Code/DB): AspDotNetStorefront MSx 9.1.0.1/9.1.0.0
    Execution Mode: 64 Bit
    Dot Net Developments - E-commerce By Experience