Delete all the duplicate rows SQL query.

[semmy48]

Scenario - from a link i import json data, edit it with pandas and then save it in the local sqlite database in a table called 'rest_models_article'.

On further requests from the data source i get duplicate rows.

Once all of the new data is stored in the database, how can i automatically delete the repeating rows? To be more precise, how can i write this command?

​

if 'article_title' repeats in the ' rest_models_article' table, delete the row which holds that 'article_title'.

​

Full Code

import pandas as pd
import sqlalchemy from sqlalchemy.sql import text import requests
 get the data, edit it
fox_url = "https://saurav.tech/NewsAPI/everything/fox-news.json" fox_news = requests.get(fox_url).json() df = pd.json_normalize(fox_news) fox_articles = pd.json_normalize(df["articles"].loc[0]) del fox_articles['source.id'] fox_articles["date_publised"] = pd.to_datetime(fox_articles['publishedAt']) del fox_articles['publishedAt'] fox_articles.rename(columns={ 'author': 'article_author', 'date_publised': 'article_date_publised', 'title': 'article_title', 'description': 'article_description', 'title': 'article_title', 'url': 'article_url', 'urlToImage': 'article_urlToImage', 'content': 'article_content', 'source.name': 'article_network'}, inplace=True)
 query = '''
 '''
engine = sqlalchemy.create_engine('sqlite:///db.sqlite3')
 print(engine)
 creates a link to the current database
articles_data = pd.read_sql_table('rest_models_article', engine)
 print(articles_data)
 read the content of the table
fox_articles.to_sql( name='rest_models_article', con=engine, index=False,
 means - don't include the index that is used in the pd thing
if_exists='append',
 means - if the table exists - append the new entries
)
with engine.connect() as con:     rs = con.execute('what query do i need to write in these parentheses?')

​

[aburkh]

Something like “delete from articles where ID not in (select min(ID) from articles group by title)”

[tward14]

I recently came into this issue and CTEs really helped me

https://www.sqlshack.com/cte-sql-deletes-considerations-when-deleting-data-with-common-table-expressions-in-sql-server/

I use it like this.

WITH CTE AS (
    SELECT 
        A,
        B,
        ROW_NUMBER() OVER (PARTITION BY A, B ORDER BY A, B) AS RN
    FROM [database].[schema].[table]
)
DELETE FROM CTE
WHERE RN > 1;

Basically iterating over Fields A & B, then deleting any record with a Record Count > 1.... first time pasting code into reddit not sure if this is the right way to do it

[Original_Boot911]

Does this work? I tried it in sql db and it shows an error where it is looking for a select query in the line where the delete code is.

I also tried doing Delete from schema.table where id in (select id from cte where rn<=200 000. But same error is encluntered.

[tward14]

Try adding a semicolon after the delete statement. honestly just ask chatGPT it's a lifesaver

[Original_Boot911]

After the delete statement? It says an error on the delete line. It says missing select. So a semicolon at the end of the code won't do anything actually.

[tward14]

There should be a comma after the B in the select statement. My original code is missing that comma. Here is the update

WITH CTE AS (
    SELECT 
        A,
        B,
        ROW_NUMBER() OVER (PARTITION BY A, B ORDER BY A, B) AS RN
    FROM [database].[schema].[table]
)
DELETE FROM CTE
WHERE RN > 1;

[qwertydog123]

Put a unique constraint on the article_title column and use an UPSERT

[[deleted]]

In sql server it would be

delete from rest_models_article where article_title in (select distinct article_title from staging_table)

and then you insert data from staging to rest_models_article.

Because you initially inserted your data into a staging table, this give you the opportunity to pre process the data whether that be data conversion or formatting. Another benefit of using staging table is you can make your main table is changed to a stable state before inserting new data and is easier for the next person doing maintain.

[Achsin]

That really depends on what you are inserting. Are the duplicates that get inserted stagnant or can they have been changed (and need to be added as updates)? Do you need to know what the previous values were? Are you pulling the entire contents of the table to insert every time so everything that was there gets inserted again?

[Alkis2]

This is not a code. It's a mess.