Solved

UPDATE table field based on SELECT statement

Posted on 2010-08-13
3
359 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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