Solved

UPDATE table field based on SELECT statement

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now