conditional update query syntax
-
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.CompanyNameTable2.Description
--------
Some Text A
Some Text B
--------
Table2.mfg
empty
emptyTable1.CompanyName
---------
Company1
Company2
Company3 -
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.
Larnu.uk -
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 blahtable2.mfg
[empty]table1.CompanyName
SEARS
LOWES
HOME DEPOT
WALMARTthe 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 DEPOTThanks for your help.
-
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 -
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, targettable2
ID, Description, Mfg
150, blah blah blah blah blah, null
151, blah blah home depot blah, null
152 ,blah blah lowes blah blah, nullafter 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, lowesis this any clearer?
- paulgq - Friday, August 17, 2018 8:25 PM
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 thisCREATE TABLE #Table1 (
ID int NOT NULL
, CompanyName varchar(20) NOT NULL PRIMARY KEY -- CompanyName in table1 does not allow duplicates
);
GOINSERT INTO #Table1 ( ID, CompanyName )
VALUES ( 100, 'sears' )
, ( 101, 'walmart' )
, ( 102, 'home depot' )
, ( 103, 'lowes' )
, ( 103, 'target' );
GOCREATE 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 );
GOSELECT 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 ); -
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.
-
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.
- paulgq - Saturday, August 18, 2018 7:52 PM
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 - paulgq - Saturday, August 18, 2018 7:52 PM
Oh shoot. I just noticed that you are using Access. Then my post with the splitter function will not help you.
- paulgq - Saturday, August 18, 2018 7:52 PM
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.
-
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.
- 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 #Table1Insert Into #Table1(txt) Values
Create Table #Table1(id int identity(1,1), txt varchar(100), productcode varchar(100))
('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) > 0Select * From #Table1
- paulgq - Sunday, August 19, 2018 11:48 AM
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.
- paulgq - Sunday, August 19, 2018 11:48 AM
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
);
GOINSERT INTO #Table1 ( ID, CompanyName )
VALUES ( 100, 'sears' )
, ( 101, 'walmart' )
, ( 102, 'home depot' )
, ( 103, 'lowes' )
, ( 103, 'target' );
GOCREATE 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 );
GOUPDATE t2
SET t2.Mfg = t1.CompanyName
FROM #Table2 t2
CROSS APPLY(SELECT TOP(1) *
FROM #Table1 t1
WHERE t2.Description LIKE '%' + t1.CompanyName + '%') t1SELECT *
FROM #Table2
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply