April 22, 2009 at 7:50 am
I have a table with a whole lot of information in it, it also has some duplicate information thanks to user entry. It has an ID column that is numerical and incremented itself by 1 every time a new record was inserted. I created a query to find all of the records that have the same city, state, and zip, these are the records I need to update. My problem is these records all have unique ID's on them that tie them to incorrect information. I would like to update the records so that if the city, state, zip are similar for any amount of records it would take the max of those records and apply it to all. Thanks!
April 22, 2009 at 7:53 am
How about some table definitions and test data? See the link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 22, 2009 at 7:57 am
You have to do a self join with a subquery to give you the max.
If you can post some sample code to create a table and insert some sample records it would be easier to provide you with an answer. Something that would allow someone to fully understand what are you trying to do.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 22, 2009 at 8:13 am
CREATE TABLE [dbo].[Project](
[projectID] [int] NOT NULL,
[name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[projectNumber] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[statusID] [smallint] NULL,
[statusDate] [datetime] NULL,
[lostWonReasonID] [smallint] NULL,
[scheduleDifferenceInWeeks] [decimal](18, 0) NULL,
[cema] [bit] NULL,
[salesmanID] [int] NULL,
[siteID] [int] NULL,
[shipToAddressID] [int] NULL,
[soldToAddressID] [int] NULL,
[businessUnitID] [smallint] NULL,
[fieldOfficeID] [smallint] NULL,
[districtOfficeID] [smallint] NULL,
[tversion] [timestamp] NULL
Each row has a unique siteID and it is what I want to be the same for if the city, state, zip all match for that name value.
I also do a join with another table to get the full address here is my query that gives me what I need to figure out how many rows need to be updated.
--Gets all info where duplicate names exist
select * from project, address
where project.shipToAddressID = address.addressID
and name in (select name from project, address
where project.shipToAddressID = address.addressID
group by name, city, state, zip
having count(*) > 1)
and city != ''
April 22, 2009 at 1:29 pm
Sorry, had too many meetings.
Let me clarify something.
If I understand properly you want to update the siteID to be the value found in a record with the highest projectID among records with the same city, state and zip linked by shiptoaddressID. Correct?
Can you also provide some data from the address table?
PS.
I also think you do not have to bother with City and state because ZIP should be unique enough.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 22, 2009 at 1:37 pm
You would think ZIP would be enough but you don't know my users and how they can't be bothered to input that data.
Here is what I have for a solution. This gives me what I want, I then insert it into a temp table and populate/update my source tables with the results of the temptable.
select customer.name, address.city, address.state, address.zip, max(project.siteid) from project, address, sitemaster, customer
where project.shipToAddressID = address.addressID
and project.siteID = sitemaster.siteID
and customer.companyID = sitemaster.companyID
and customer.name in (select name from project, address
where project.shipToAddressID = address.addressID
group by project.name
having count(*) > 1)
and address.city != ''
group by customer.name, address.city, address.state, address.zip
My main problem was that I was using an aggregrate in my select and I had it in my last group by...having it in my last group by screwed up the aggregrate. Thanks again.
-Nate
April 22, 2009 at 1:44 pm
Doesn't it feel better when you find the solution yourself?
😀
Good luck and come back if you have more issues...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply