Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

FSUKXAZ
Starting Member

3 Posts

Posted - 2014-08-05 : 10:17:06

Hi,

I'm writing a Coldfusion page to perform some DB cleanup. I'm needing some assistance with a query.

SELECT ID, Vend_ID, Comp, Zip_Code, Type, Work, Updated
FROM Vend_Comp
WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)

Now that park works fine. What I need is something to handle the returned records. I need to loop through results and select any with duplicate Vend_ID. Then delete the ones with the lower ID numbers.

Perhaps a sub query to pull out the one with duplicate Vend_ID... then a query to get Max(ID) and delete the ones <> to Max(ID)? Hmmmm...

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-05 : 10:32:46

--Assuming that ID and updated are also duplicate values: all columns can be duplicated except vendid -- this should leave the min vend_id

DELETE FROM Vend_Comp
WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
AND Vend_ID NOT IN (SELECT MIN(Vend_ID) FROM Vend_Comp
WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
GROUP BY ID,Comp, Zip_Code, Type, Work, Updated
)

FSUKXAZ
Starting Member

3 Posts

Posted - 2014-08-05 : 10:47:27

ID is the Primary key field with no duplicates. Updated is a bit field 1 or 0. So, ID is the only field that can not be duplicated.

quote:
Originally posted by MichaelJSQL

--Assuming that ID and updated are also duplicate values: all columns can be duplicated except vendid -- this should leave the min vend_id

DELETE FROM Vend_Comp
WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
AND Vend_ID NOT IN (SELECT MIN(Vend_ID) FROM Vend_Comp
WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
GROUP BY ID,Comp, Zip_Code, Type, Work, Updated
)

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-05 : 13:53:50

So you are trying to just keep the lowest ID for Duplicate Vend_ID,Comp, Zip_Code, Type, Work, Updated. If so the following: If not post what you look at to determine duplicates and what is unique suchs as ID

DELETE FROM Vend_Comp
WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
AND ID NOT IN (SELECT MIN(ID) FROM Vend_Comp
WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
GROUP BY Vend_ID,Comp, Zip_Code, Type, Work, Updated
)

FSUKXAZ
Starting Member

3 Posts

Posted - 2014-08-05 : 16:08:30

Thank you so much for you help.

I got the solution off another forum. The final solution was looping over Zip_Code using a dynamic value and then a nested loop over Work using a dyanmic value. Then an update query. The initial Zip_Code query checks to see if Updated = 0 so it doesn't go back over record if it was already updated.

WITH Rownumbers AS(
SELECT ID, Vend_ID, Comp, Zip_Code, Type, Work, Updated,
ROW_NUMBER() OVER( PARTITION BY Vend_ID ORDER BY ID DESC) rn
FROM Vend_Comp
WHERE Type = 'Poly'
AND Work = '#j#'
AND Zip_Code = '#GetAllSecondaryZips.Zip[i]#')
DELETE
FROM Rownumbers
WHERE rn > 1;

UPDATE Vend_Comp
SET UPDATED = 1
WHERE Type = 'Poly'
AND Work = '#j#'
AND Zip_Code = #GetAllSecondaryZips.Zip[i]#

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources