0

I have a sqlite3 database in a rails app. It contains amongst others these columns: name, telephone, latitude, longitude.

These are all strings. These correspond to businesses. I noticed that many entries in the database are duplicates.

How do I remove these from my rails database? Do I need to write SQL or is there a function in rails that takes in column names and removes entries that have similar values in those columns.

If SQL is the best way, how should my query look like.

I tried the following query but my database browser crashed:

SELECT * 
FROM listings L1, listings L2
WHERE L1.name = L2.name
AND L1.telephone = L2.telephone

EDIT: I found this post helpful. But still looking for a solution thats airtight.

Community
  • 1
  • 1
banditKing
  • 9,405
  • 28
  • 100
  • 157
  • Database browser may crash for another reason. Query looks ok, but you may try changing select to `L1.* L2.*` and adding `L1.id != L2.id` to `WHERE` – kirilloid Mar 25 '12 at 02:48
  • here is the query:----->SELECT * FROM listings L1, listings L2 WHERE L1.* = L2.* AND L1.id != L2.id------>Gives error near "*": syntax error – banditKing Mar 25 '12 at 02:58
  • 1
    You cannot write `WHERE L1.* = L2.*`. I meant change **what** you select from `*` to `L1.*, L2.*`, not `WHERE` condition. – kirilloid Mar 25 '12 at 03:05

0 Answers0