Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

UPDATE table field based on SELECT statement

Posted on 2010-08-13
3
Medium Priority
?
362 Views
Last Modified: 2012-05-10
Hi Experts, please assist here; I am trying to UPDATE a field in a source table from the result of a calculated field (Step 5)

In SQL, I want to say
 UPDATE #SourceTable SET IsDuplicate = IsDuplicate from query in Step 5

Included below are queries to create and populate the tables to match my real table.

Thanks!!

-- STEP 1. This is the source table with property records.
CREATE TABLE #SourceTable(
      ID int IDENTITY
      ,CountyNumber int NULL
      ,PropertyAddress varchar(150) NULL
      ,City varchar(50) NULL
      ,State varchar (50) NULL
      ,DataSource int NULL
      ,PropertyTypeKey int NULL
      ,IsDuplicate bit NULL
      )

-- STEP 2: insert sample data
INSERT INTO #SourceTable(CountyNumber, PropertyAddress, City, State, DataSource, PropertyTypeKey)
      VALUES(123445, '1201 Startel Blvd', 'Phoenix', 'AZ', 2, 34)
                  ,(230087,'1234 Lakeview Terrace', 'Fargo', 'ND', 2, 45)
                  ,(234567,'1234 Lakeview Terrace', 'Fargo', 'ND', 3, 45)
                  ,(435678, '1234 Lakeview Terrace', 'Fargo', 'ND', 4, 45)
                  ,(567890, '167 Manatee Court', 'Provo', 'UT',5, 22)

---STEP 3: split the source data and populate the dependent tables
SELECT ID, CountyNumber, PropertyAddress, City, State INTO #DimProperty FROM #SourceTable
SELECT ID, CountyNumber, DataSource, PropertyTypeKey INTO #FactProperty FROM #SourceTable

-- STEP 4: confirm the records are correctly entered.
SELECT * FROM  #SourceTable
SELECT * FROM #DimProperty
SELECT * FROM #FactProperty

/*
STEP 5: this query checks for duplicate entries and marks the non-county records as 1 (Is Duplicate). It works as expected
 Reqirement is to update the value of IsDuplicate in the #SourceTable (#SourceTable.IsDuplicate) to match the value of "IsDuplicate" in this query
*/

SELECT DISTINCT dlis.CountyNumber, dlis.PropertyAddress, dlis.City, f.PropertyTypeKey
,CASE f.DataSource
            WHEN 2 THEN 'County Records'
            WHEN 3 THEN 'Archives'
            WHEN 4 THEN 'Owner Provided'
            WHEN 5 THEN 'Owner & State Info'
            ELSE 'Unknown'
      END AS 'DataSource'
      ,CASE
            WHEN ( f.DataSource != 2 ) THEN 1            
       END AS 'IsDuplicate'
 FROM (
       SELECT PropertyAddress, City      ,COUNT(PropertyAddress + ' ' + City) 'Count'
            FROM #SourceTable t
            GROUP BY PropertyAddress, City
            HAVING COUNT(PropertyAddress + ' ' + City) > 1 ) result
            JOIN #DimProperty dlis WITH (NOLOCK)
ON result.PropertyAddress = dlis.PropertyAddress
      JOIN #FactProperty f
ON dlis.ID = f.ID
            WHERE (DataSource IN (2, 3) OR DataSource IN (2, 4) OR DataSource IN (2, 5) )             
ORDER BY PropertyAddress


 --DROP TABLE #DimProperty
 --DROP TABLE #FactProperty
 --DROP TABLE #SourceTable
0
Comment
Question by:godwineffiong
  • 2
3 Comments
 

Author Comment

by:godwineffiong
ID: 33432079
I should mention that the CountyNumber is not unique. Different properties from same or different counties can have the same CountyNumber.
0
 

Accepted Solution

by:
godwineffiong earned 0 total points
ID: 33432577
I figured it out.  Below is solution query:
 UPDATE #SourceTable
SET IsDuplicate = 1
WHERE PropertyAddress IN (
SELECT t.PropertyAddress FROM(
SELECT PropertyAddress, City,COUNT(PropertyAddress + ' ' + City) 'Count'
FROM #SourceTable
GROUP BY PropertyAddress, City
HAVING COUNT(PropertyAddress + ' ' + City) > 1
) t )
AND DataSource != 2
 
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33435481
I suspect you may find this a lot more efficient:
UPDATE	t
SET	IsDuplicate = 1
FROM	#SourceTable t
	INNER JOIN (
		SELECT	PropertyAddress
		FROM	#SourceTable
		GROUP BY 
			PropertyAddress,
			City
		HAVING COUNT(*) > 1) d ON t.PropertyAddress = d.PropertyAddress
WHERE	t.DataSource != 2 

Open in new window

0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question