remove duplicate rows based on criteria in SQL Server 2000
Posted on 2011-02-14
Hi, I have some data in SQL where I need to remove duplicates based on certain criteria. A sample of the data is follows:-
Property Item Date Location
100 G7FIRE 10/10/2010 LOUNGE
100 G6BBU 11/10/2010 LOUNGE
100 G7FIRE 12/10/2010 KITCHEN
101 G6BBU 13/10/2010 LOUNGE
102 G7FIRE 14/10/2010 KITCHEN
105 G7FIRE 10/10/2010 LOUNGE
105 G6BBU 11/10/2010 LOUNGE
105 G7FIRE 12/10/2010 KITCHEN
For properties 100 and 105 there is a G6BBU and a G7FIRE in the SAME location of LOUNGE. If this is the case then I need to find and remove the G7FIRE.
So for properties where the is a G6BBU and a G7FIRE in the SAME LOCATION (which could be lounge, kitchen, anywhere) I need to remove the G7FIRE line?
Is there an update query that I can run to apply this?