conditional update query syntax

  • paulgq

    SSC Enthusiast

    Points: 110

    I have a question about the syntax of an update query.  Here is my layout and what I need to do.  It seems simple enough.  I'm doing it now with the help of of an ASP front end and it works but takes forever due to overhead.  I'd like to do it right inside SQL alone. 

    For each record in Table 2, I need to check if the description field contains any text that matches any of the entries in Table 1.  If so, then set the value of that match to the mfg field in table 2.

    so something like:

    set table2.mfg with value of table1.CompanyName
    where table2.Description contains (with wildcards) any values from table1.CompanyName

    Table2.Description
    --------
    Some Text A
    Some Text B
    --------
    Table2.mfg
    empty
    empty

    Table1.CompanyName
    ---------
    Company1
    Company2
    Company3

  • Thom A

    SSC Guru

    Points: 98090

    I'm not sure I follow your sample data I'm afraid; is that 2 tables or 3? What is the relationship between the 2/3 tables? What is the expected results here as well?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • paulgq

    SSC Enthusiast

    Points: 110

    2 tables

    I need to inspect each table2.Description field to see if any value contains any of the values that are present in table1.CompanyName.

    table2.description
    blah blah blah blah HOME DEPOT blah blah

    table2.mfg
    [empty]

    table1.CompanyName
    SEARS
    LOWES
    HOME DEPOT
    WALMART

    the query should result in an update to table2.mfg since HOME DEPOT is present within value of table2.description and exists in the results of  table1.CompanyName

    end result

    table2.mfg
    HOME DEPOT

    Thanks for your help.

  • drew.allen

    SSC Guru

    Points: 76425

    It would help if you provided sample data as outlined in the first link in my signature.  It's confusing, because you're presenting single columns from one table instead of presenting the whole table.  Please make sure that your sample data is readily consumable.

    Also, what do you want to do if a record in table 2 matches multiple records in table 1.  We need to account for that possibility even if you're 100% sure that it will never happen.  End users are very creative in finding ways to do things that aren't possible.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • paulgq

    SSC Enthusiast

    Points: 110

    This is the best I can do right now even though your guide says not to do it this way.  CompanyName in table1 does not allow duplicates.

    table1
    ID, CompanyName
    100, sears
    101, walmart
    102, home depot
    103, lowes
    103, target

    table2
    ID, Description, Mfg
    150, blah blah blah blah blah, null
    151, blah blah home depot blah, null
    152 ,blah blah lowes blah blah, null

    after the query runs, I need table2 to be updated in the following manner
    table2
    ID, Description, Mfg
    150, blah blah blah blah blah, null
    151, blah blah home depot blah, home depot
    152 ,blah blah lowes blah blah, lowes

    is this any clearer?

  • DesNorton

    SSC-Insane

    Points: 22488

    paulgq - Friday, August 17, 2018 8:25 PM

    This is the best I can do right now even though your guide says not to do it this way.  CompanyName in table1 does not allow duplicates.

    table1
    ID, CompanyName
    100, sears
    101, walmart
    102, home depot
    103, lowes
    103, target

    table2
    ID, Description, Mfg
    150, blah blah blah blah blah, null
    151, blah blah home depot blah, null
    152 ,blah blah lowes blah blah, null

    after the query runs, I need table2 to be updated in the following manner
    table2
    ID, Description, Mfg
    150, blah blah blah blah blah, null
    151, blah blah home depot blah, home depot
    152 ,blah blah lowes blah blah, lowes

    is this any clearer?

    What the guys are asking for, is consumable data, so that we can better understand your requirements.
    In the future please format your data like this
    CREATE TABLE #Table1 (
      ID int NOT NULL
    , CompanyName varchar(20) NOT NULL PRIMARY KEY -- CompanyName in table1 does not allow duplicates
    );
    GO

    INSERT INTO #Table1 ( ID, CompanyName )
    VALUES ( 100, 'sears' )
      , ( 101, 'walmart' )
      , ( 102, 'home depot' )
      , ( 103, 'lowes' )
      , ( 103, 'target' );
    GO

    CREATE TABLE #Table2 (
      ID int NOT NULL
    , [Description] varchar(100) NOT NULL
    , Mfg varchar(20) NULL
    );
    GO
    INSERT INTO #Table2 ( ID, [Description], Mfg )
    VALUES ( 150, 'blah blah blah blah blah', null )
      , ( 151, 'blah blah home depot blah', null )
      , ( 152 ,'blah blah lowes blah blah', null )
    --------------------------------------------------------
    -- I have added these, as you may want to think about how to handle these cases
      , ( 501, 'blah blahhome depotblah', null )
      , ( 502, 'blah blah homedepot blah', null )
      , ( 503, 'blah blahhomedepotblah', null )
      , ( 504 ,'blah blahlowes blah blah', null )
      , ( 505 ,'blah blah lowesblah blah', null )
      , ( 506 ,'blah blah home depot blah lowes blah', null )
      , ( 507 ,'blah blah homedepot blah lowes blah', null );
    GO

    SELECT ID, [Description], Mfg
    FROM (
      VALUES ( 150, 'blah blah blah blah blah', null )
       , ( 151, 'blah blah home depot blah', 'home depot' )
       , ( 152 ,'blah blah lowes blah blah', 'lowes' )
      ) AS ExpectedResults( ID, [Description], Mfg );

  • DesNorton

    SSC-Insane

    Points: 22488

    This will work for the data that you supplied.
    However, it makes the assumption that there will only ever be 1 match.
    UPDATE t2
    SET Mfg = t1.CompanyName
    FROM #Table2 AS t2
    INNER JOIN #Table1 AS t1
     on t2.[Description] LIKE '% ' + t1.CompanyName + ' %'
    WHERE t2.Mfg IS NULL;

    You may need to think about how you want to handle the other cases that I added into the sample data.

  • paulgq

    SSC Enthusiast

    Points: 110

    that was perfect.  In access, the syntax is different but I got the idea and ported it.  This is processing 90k descriptions against 12k mfgs and completes the query in 28 minutes.  Switching over to our production SQL server should see this time reduced by a third.  Very pleased.  It's processing 54 descriptions against 12k mfgs every second.

    thanks for your help.

  • DesNorton

    SSC-Insane

    Points: 22488

    paulgq - Saturday, August 18, 2018 7:52 PM

    that was perfect.  In access, the syntax is different but I got the idea and ported it.  This is processing 90k descriptions against 12k mfgs and completes the query in 28 minutes.  Switching over to our production SQL server should see this time reduced by a third.  Very pleased.  It's processing 54 descriptions against 12k mfgs every second.

    thanks for your help.

    That seems agonisingly slow.  Using a splitter function may give you better performance.
    Read this article by Jeff Moden, and download the code at the end of the article.
    With the DelimitedSplit8K function installed, the following code may give you better performance.
    WITH cteSplitData AS (
    -- The special cases for [home depot] is because you are using sapces to delimit your text,
    -- but there is also a space in the name that you are looking for. You will have to do the
    -- same for each Companyname that has space(s) in it. This will have a negative impacty on the performance.
    SELECT t2.ID, t2.Mfg, Item = REPLACE(ds.Item, 'homedepot', 'home depot')
    FROM #Table2 AS t2
    CROSS APPLY dbo.DelimitedSplit8K(REPLACE(t2.[Description], 'home depot', 'homedepot'), ' ') AS ds
    WHERE t2.Mfg IS NULL
    )
    UPDATE cte
    SET Mfg = t1.CompanyName
    FROM cteSplitData AS cte
    INNER JOIN #Table1 AS t1
    on cte.Item = t1.CompanyName

  • DesNorton

    SSC-Insane

    Points: 22488

    paulgq - Saturday, August 18, 2018 7:52 PM

    that was perfect.  In access, the syntax is different but I got the idea and ported it.  This is processing 90k descriptions against 12k mfgs and completes the query in 28 minutes.  Switching over to our production SQL server should see this time reduced by a third.  Very pleased.  It's processing 54 descriptions against 12k mfgs every second.

    thanks for your help.

    Oh shoot.  I just noticed that you are using Access.  Then my post with the splitter function will not help you.

  • andycadley

    SSCertifiable

    Points: 5261

    paulgq - Saturday, August 18, 2018 7:52 PM

    that was perfect.  In access, the syntax is different but I got the idea and ported it.  This is processing 90k descriptions against 12k mfgs and completes the query in 28 minutes.  Switching over to our production SQL server should see this time reduced by a third.  Very pleased.  It's processing 54 descriptions against 12k mfgs every second.

    thanks for your help.

    Are you using Access to prototype SQL Server code? That's going to cause you a world of hurt long term. Do yourself a favour and install either SQL Express or SQL Server Developer Edition, ideally using the same version (and preferably updates) as your server, so you can develop code using the platform you're going to be deploying to.

  • paulgq

    SSC Enthusiast

    Points: 110

    OK, thanks for everyone's input.  I am prototyping the workflow of this project, not necessarily prototyping code.   While this may seem slow, it's alleviating a worker the added responsibility to manually inspect descriptions by hand.  Now this person can get back to their primary duty and inspect a result report at the end of the week.

    Desnorton, were you suggesting that there is another delimiter that is less costly?  Or is it that removing delimiters altogether improves performance?  If I mash the description together I am concerned about short lettered names like Ace.  Ace could also be present elsewhere in the following title "Space heaters model number A123-P-007 on sale for $99 at Walgreens".  In this case, the ace in space may trigger the wrong retailer.

    We are also using the same concept to extract the part number from the description.  However, I am using a vbscript front end to break the description into an array and parse the part number from any element that is over 7 alpha numeric characters long.  This web based approach is taking 5 seconds per entry which is painfully slow.

    Is there any way to have SQL create an array from the description field, inspecting the array elements, and update the part number field with the first value for any element greater than 7 characters that contains at least 1 number?  That would be really awesome.

  • andycadley

    SSCertifiable

    Points: 5261

    It's more the case that while there are fast ways to break up a string on a character like a space, it's problematic if the text you're trying to find might also have spaces in it. You either have to preprocess it (as in Desnorton's example where "home depot" first gets replaced by "homedepot") or use a slower technique to find substrings in the text.

    A similar technique to finding vendor names can be used to find product codes, e.g.

    Drop Table If Exists #Table1
    Create Table #Table1(id int identity(1,1), txt varchar(100), productcode varchar(100))
    Insert Into #Table1(txt) Values
    ('Space heaters model number A123-P-007 on sale for $99 at Walgreens'),
    ('Ace heaters product P43522AFC available for $221')

    Select * From #Table1

    Update T
    Set productcode = PC.Item
    From #Table1 T
    Cross Apply dbo.DelimitedSplit8K_LEAD(txt, ' ') PC
    Where
    Len(PC.Item) > 7
    And PATINDEX('%[0-9]%', PC.Item) > 0

    Select * From #Table1

  • DesNorton

    SSC-Insane

    Points: 22488

    paulgq - Sunday, August 19, 2018 11:48 AM

    OK, thanks for everyone's input.  I am prototyping the workflow of this project, not necessarily prototyping code.   While this may seem slow, it's alleviating a worker the added responsibility to manually inspect descriptions by hand.  Now this person can get back to their primary duty and inspect a result report at the end of the week.

    Desnorton, were you suggesting that there is another delimiter that is less costly?  Or is it that removing delimiters altogether improves performance?  If I mash the description together I am concerned about short lettered names like Ace.  Ace could also be present elsewhere in the following title "Space heaters model number A123-P-007 on sale for $99 at Walgreens".  In this case, the ace in space may trigger the wrong retailer.

    We are also using the same concept to extract the part number from the description.  However, I am using a vbscript front end to break the description into an array and parse the part number from any element that is over 7 alpha numeric characters long.  This web based approach is taking 5 seconds per entry which is painfully slow.

    Is there any way to have SQL create an array from the description field, inspecting the array elements, and update the part number field with the first value for any element greater than 7 characters that contains at least 1 number?  That would be really awesome.

    I was not suggesting a less costly delimiter.  What I was trying to convey, is that when the delimiter is contained in the name, then it will break the name up and thus not recognise the full name.

    Jeff's function that I pointed you to will split the string, with an indicator of the position within the string.  You could then query each element to see if it matches any of your logic.

  • Jonathan AC Roberts

    SSCoach

    Points: 16489

    paulgq - Sunday, August 19, 2018 11:48 AM

    OK, thanks for everyone's input.  I am prototyping the workflow of this project, not necessarily prototyping code.   While this may seem slow, it's alleviating a worker the added responsibility to manually inspect descriptions by hand.  Now this person can get back to their primary duty and inspect a result report at the end of the week.

    Desnorton, were you suggesting that there is another delimiter that is less costly?  Or is it that removing delimiters altogether improves performance?  If I mash the description together I am concerned about short lettered names like Ace.  Ace could also be present elsewhere in the following title "Space heaters model number A123-P-007 on sale for $99 at Walgreens".  In this case, the ace in space may trigger the wrong retailer.

    We are also using the same concept to extract the part number from the description.  However, I am using a vbscript front end to break the description into an array and parse the part number from any element that is over 7 alpha numeric characters long.  This web based approach is taking 5 seconds per entry which is painfully slow.

    Is there any way to have SQL create an array from the description field, inspecting the array elements, and update the part number field with the first value for any element greater than 7 characters that contains at least 1 number?  That would be really awesome.

    There aren't arrays in SQL Server. There are tables which are very similar.
    It would be helpful if you would read Desnorton's comment and provide some data. I'm sure you will get the answer you are looking for very quickly then.
    This might come up with the answer you want:
    CREATE TABLE #Table1 (
    ID int NOT NULL
    , CompanyName varchar(20) NOT NULL PRIMARY KEY -- CompanyName in table1 does not allow duplicates
    );
    GO

    INSERT INTO #Table1 ( ID, CompanyName )
    VALUES ( 100, 'sears' )
     , ( 101, 'walmart' )
     , ( 102, 'home depot' )
     , ( 103, 'lowes' )
     , ( 103, 'target' );
    GO

    CREATE TABLE #Table2 (
    ID int NOT NULL
    , [Description] varchar(100) NOT NULL
    , Mfg varchar(20) NULL
    );
    GO
    INSERT INTO #Table2 ( ID, [Description], Mfg )
    VALUES ( 150, 'blah blah blah blah blah', null )
     , ( 151, 'blah blah home depot blah', null )
     , ( 152 ,'blah blah lowes blah blah', null )
    --------------------------------------------------------
    -- I have added these, as you may want to think about how to handle these cases
     , ( 501, 'blah blahhome depotblah', null )
     , ( 502, 'blah blah homedepot blah', null )
     , ( 503, 'blah blahhomedepotblah', null )
     , ( 504 ,'blah blahlowes blah blah', null )
     , ( 505 ,'blah blah lowesblah blah', null )
     , ( 506 ,'blah blah home depot blah lowes blah', null )
     , ( 507 ,'blah blah homedepot blah lowes blah', null );
    GO

    UPDATE t2
    SET t2.Mfg = t1.CompanyName
    FROM #Table2 t2
    CROSS APPLY(SELECT TOP(1) *
         FROM #Table1 t1
                 WHERE t2.Description LIKE '%' + t1.CompanyName + '%') t1

    SELECT *
    FROM #Table2

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply