How can I delete duplicate rows in SQL? HELP!

Hi!
I tried inserting values into a table and when I executed, it did not show me that a row was affected… so I ran it again. Then when I did select * from table, it showed me that it was entered twice. I tried deleting by the GUI method of right click, edit top 200 rows, but it does not let me delete it saying that 2 rows are affected or something. HOW can I delete the duplicate row and still keep one? It is really annoying.


you can see in the photo for addressID 6 and 7 they are both repeated twice. thank you!

if your rows have ids, you could delete by id:

DELETE FROM practiceaddress where id = 6;

or you could delete based on addressID and simple LIMIT 1, which will, well does it need explaining? Think its pretty self explanatory.

limit only works for mysql or some other type, I’m using sql server. I know there’s “top” for sql server but it gives me an error. As for delete from practiceaddress where id=6, I’m aware of that, thank you. However, it will delete both records. I was trying to delete one while keeping the other, not delete both.

the id (identifiers) are unique. What weird kind of weird dialect is sql server using? How can deleting something by a unique identifier results in the deletion of multiple rows?

granted, deleting by addressID would be problematic. Is addressID an foreign key and do you have on_cascade delete?

I also find the naming convention weird, maybe because I am used to mySQL. I would name the table practice_address, and the column address_id. An be consistent with uppper- vs lower-case

had I known that you used SQL server (which is not mentioned in the original topic anywhere), I might not have responded, I assumed we talked about mySQL or something (given that is what codecademy teaches)

Yes, you are right, I did not specify that it was sqlserver. I am able to delete from the table where addressid=6, but then it will delete both of them as both are identified by addressid=6 if you look at the photo. It executed twice so both records containing the same information/data were saved as addressID=6.

I don’t have a foreign key or anything set up yet, I was just playing around with one table I created. I’m not sure what on_cascade delete is :frowning: sorry I’m new

the first column of your table is id, right? this column is unique, so deleting by this column:

DELETE FROM practiceaddress where id = 6;

should only delete a single row. Look closely, I use id here, not address_id.

also, like you said, TOP should work. According to stackoverflow:

Delete the 'first' record from a table in SQL Server, without a WHERE condition - Stack Overflow

if you don’t care with row gets deleted, TOP 1 should work, otherwise you can use a subquery to determine the order

So there are two ways to solve the problem, and both should work.