Solved

UPDATE table field based on SELECT statement

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Suggested Solutions

Title # Comments Views Activity
Query to return total 6 22
Count with a subquery showing details 10 49
date diff with Fiscal Calendar 4 55
Disable TLS1.0 on Win 2012 server 7 29
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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

829 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