delete duplicates in sql lite from one table

Question

I want to delete duplicates form my table in database.

Databasename - >  urls
Table - > url

I tried this, but its not working:

SELECT DISTINCT
 url
FROM
 urls;

Table structure:

url    website    checked

This link may be useful to you https://stackoverflow.com/q/4685173/2893413 — Sadikhasan, Dec 04 '18 at 10:57
Add your table structure some table results which has duplicates. — Zeki Gumus, Dec 04 '18 at 10:58
It's unclear if you are using SQLite or MySQL. They are two different products. — Kamil Gosciminski, Dec 04 '18 at 11:04

score 0 · Answer 1 · answered Dec 04 '18 at 11:00

You don't SELECT from a database. You perform a query to read rows from a table, this is why your query is wrong in the way that you're trying to select a table from database.

This is not how it's done. You should connect to a database and then select columns from a table.

Let's say you have duplicates in column website stored within table url residing in database urls, so first you would connect to your database, assuming you are using command line tool (if that's not the case, just choose it in your graphical query tool):

USE urls; -- for MySQL
.open "urls.db" -- for SQLite

Then select unique websites from url table like this:

SELECT DISTINCT website
FROM url;

If you need to return unique rows (not one column values) then use *:

SELECT DISTINCT *
FROM url;

score 0 · Answer 2 · answered Dec 04 '18 at 11:00

0

Here 'url' is your table name, so it should come after the keyword 'FROM'

Something like this:

select distinct * from url;

answered Dec 04 '18 at 11:00

Mi8Guy

37
9

score 0 · Accepted Answer · answered Dec 04 '18 at 11:13

You can always use a temporary table to make it easier for yourself to actually remove the duplicates from the table.

CREATE TABLE urls_temp 
LIKE urls;

INSERT INTO urls_temp
SELECT DISTINCT * 
FROM urls
GROUP BY url;

DROP TABLE urls;

ALTER TABLE urls_temp 
RENAME TO urls;

If you just want to get the data out of the database hiding the duplicates you can just do this:

SELECT DISTINCT *
FROM urls;

However in the long run you can ensure uniqueness by putting a constraint on the column or making it a primary key to stop anymore duplicates from being entered in the first place.

score 0 · Answer 4 · answered Dec 04 '18 at 12:10

SQL query to delete duplicate websites from url table, the table must have unique field - id

DELETE FROM url 
WHERE
    id NOT IN (SELECT 
                id
              FROM
                    (SELECT 
                        id
                    FROM
                        url
                    GROUP BY website
                    ) AS x
               )

delete duplicates in sql lite from one table

4 Answers4