How can I delete duplicate rows in SQL? HELP!

Get Help SQL
#1

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.

Screenshot (181)
Screenshot (181)1277×706 22.8 KB

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

#2

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.

#3

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.

#4

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)

#5

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.

#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

#7

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.