Link to home
Create AccountLog in
Avatar of arthurh88
arthurh88

asked on

How can I delete rows from this query?

Here is a working query that finds all rows in my table that have duplicate City and State fields.

SELECT     *
FROM       CityLIst AS a INNER JOIN
                          (SELECT     City, State
                            FROM          CityList
                            GROUP BY City, State
                            HAVING      (COUNT(*) > 1)) AS b ON a.City = b.City AND a.State = b.State


It is returning about 100 instances in my table of duplicates.  Now I want to be able to delete rows...but in my SQL Management Studio the whole table is grayed out, It wont let me delete any of the results.  Does someone know an easy way I can delete records?  I only want one instance of any row with a particular City, State.  
Avatar of James Murrell
James Murrell
Flag of United Kingdom of Great Britain and Northern Ireland image

it may be down to permissions on your access - sorry
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of arthurh88
arthurh88

ASKER

i have full permissions.  that isn't the issue.  its SQL management studio not letting me delete the rows because of the inner join.  See picture
query.jpg
cells are READ ONLY
Have you tried the delete query?
Thanks for the picture, have you tried cyberkiwi query
i  just did and it worked!  Thank you.