August 5, 2014 at 9:14 am
I'm writing a Coldfusion page to perform some DB cleanup. I'm needing some assistance with a query. ID is the Primary key Int field with no duplicates. Updated is a bit field 1 or 0. So, ID is the only field that can not be duplicated. The rest are Varchar fields.
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...
August 5, 2014 at 10:04 am
You are on the right track with your last sentence. Here's how you could do it. I'm leaving your option and an additional one.
--Your option
SELECT *
--DELETE
FROM Vend_Comp
WHERE ID NOT IN ( SELECT MAX( ID)
FROM Vend_Comp
GROUP BY Vend_ID
--WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
);
--Option 2
WITH Rownumbers AS(
SELECT ID, Vend_ID, Comp, Zip_Code, Type, Work, Updated,
ROW_NUMBER() OVER( PARTITION BY Vend_ID ORDER BY ID /*Or the column needed*/ DESC) rn
FROM Vend_Comp
--WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
)
SELECT *
--DELETE
FROM Rownumbers
WHERE rn > 1;
August 5, 2014 at 10:19 am
Allow me to cast my vote for Luis's second approach. I use the same approach myself when i need to de-dupe data. It divides the work into two parts and lets you see exactly what's going to be deleted before doing it. If you don't know about the CTE it uses, this is a great opportunity for learning.
August 5, 2014 at 1:59 pm
Thank you so much for you help. I took your 2nd option. 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 = '#GetAllZips.Zip#')
DELETE
FROM Rownumbers
WHERE rn > 1;
UPDATE Vend_Comp
SET UPDATED = 1
WHERE Type = 'Poly'
AND Work = '#j#'
AND Zip_Code = #GetAllZips.Zip#
Luis Cazares (8/5/2014)
You are on the right track with your last sentence. Here's how you could do it. I'm leaving your option and an additional one.
--Your option
SELECT *
--DELETE
FROM Vend_Comp
WHERE ID NOT IN ( SELECT MAX( ID)
FROM Vend_Comp
GROUP BY Vend_ID
--WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
);
--Option 2
WITH Rownumbers AS(
SELECT ID, Vend_ID, Comp, Zip_Code, Type, Work, Updated,
ROW_NUMBER() OVER( PARTITION BY Vend_ID ORDER BY ID /*Or the column needed*/ DESC) rn
FROM Vend_Comp
--WHERE (Type = 'Poly') AND (Work = 'Second') AND (Zip_Code = 12345)
)
SELECT *
--DELETE
FROM Rownumbers
WHERE rn > 1;
August 5, 2014 at 3:23 pm
Why are you looping? What RDBMS are you using?
August 6, 2014 at 7:23 am
There's more to it than what I posted. Coldfusion programming. I built my Coldfusion page. I had to loop over a couple dynamic variables and do 50,000 records. I just needed help with the query part. My code + your query = Success! Thanks again.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply