RE: Update query for my new boss in my new job I am struggling with

SSC Guru

Points: 186127

February 22, 2013 at 3:56 am

dwain.c (2/22/2013)
Have you tried the solution I suggested?
I'm thinking it will do what you need.
I hope I didn't confuse you by putting Table2 into the CTE.

Looks good to me. Same as this;

SET NOCOUNT ON

DROP TABLE #TABLE1

CREATE TABLE #TABLE1 (software_name_raw VARCHAR(100))

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio Viewer 2003 (English)'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio 2003 Step by Step'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio Viewer 2003 (English)'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio 2007 (Exe)'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft® Office Visio® 2007'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio 2007 Professional Edition'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio Professional 2007'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio Viewer 2007'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio 2010'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio 2010 (Exe)'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio 2010 Premium Edition'

INSERT INTO #TABLE1 (software_name_raw) SELECT 'Microsoft Office Visio 2010 Professional Edition'

DROP TABLE #TABLE2

CREATE TABLE #TABLE2 (software_name_raw VARCHAR(100), software_name_amended VARCHAR(100))

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio Viewer 2003 (English)', 'Visio 2003 Viewer'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio 2003 Step by Step', 'Visio 2003 Step by Step'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio Viewer 2003 (English)', 'Visio 2003 Viewer'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio 2007 (Exe)', 'Visio 2007'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft® Office Visio® 2007', 'Visio 2007'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio 2007 Professional Edition', 'Visio 2007 Professional'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio Professional 2007', 'Visio 2007 Professional'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio Viewer 2007', 'Visio 2007 Viewer'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio 2010', 'Visio 2010'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio 2010 (Exe)', 'Visio 2010'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio 2010 Premium Edition', 'Visio 2010 Premium'

INSERT INTO #TABLE2 (software_name_raw,software_name_amended) SELECT 'Microsoft Office Visio 2010 Professional Edition', 'Visio 2010 Professional'

-- Check using SELECT - does everything look ok?

SELECT t1.software_name_raw, t2.software_name_amended

FROM #TABLE1 t1

INNER JOIN #TABLE2 t2

ON t2.software_name_raw = t1.software_name_raw

ORDER BY t1.software_name_raw

-- nope - result set has FAR too many rows

-- Eliminate dupes in Table 2 and check again using SELECT:

SELECT t1.software_name_raw, t2.software_name_amended

FROM #TABLE1 t1

INNER JOIN (

SELECT software_name_raw,software_name_amended

FROM #TABLE2

GROUP BY software_name_raw,software_name_amended

) t2

ON t2.software_name_raw = t1.software_name_raw

ORDER BY t1.software_name_raw

-- looks ok: convert to UPDATE:

UPDATE t1 SET software_name_raw = t2.software_name_amended

FROM #TABLE1 t1

INNER JOIN (

SELECT software_name_raw,software_name_amended

FROM #TABLE2

GROUP BY software_name_raw,software_name_amended

) t2

ON t2.software_name_raw = t1.software_name_raw

-- Check the results:

SELECT * FROM #TABLE1 t1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden