Solved

UPDATE table field based on SELECT statement

Posted on 2010-08-13
3
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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