Delete duplicate information at the db level

[alex1368]

hello
I want to delete the duplicate data at the db level, please introduce me the relevant node or workflow.

[yogesh_nawale]

Hi @alex1368,
You can use DB Query node to delete data directly from your database

DB Query

for eg use this query to delete data

DELETE FROM your_table
WHERE id NOT IN (
SELECT MIN(id)
FROM your_table
GROUP BY Product
);

Regards,
Yogesh

[tobias.koetter]

Hi @yogesh_nawale and @alex1368 ,
if you want to execute a data manipulation statement that permanently alters your data in the DB please use the DB SQL Executor.
If you only want to eliminate the duplicates when retrieving the data but don’t want to do this permanently in the DB you can use the DISTINCT command e.g.

SELECT DISTINCT * FROM #table# AS "table"

in the DB Query node.

Bye
Tobias

[mlauber71]

@alex1368 if you have more complicated scenarios you can use the WINDOW function in SQL (depending on your database) to have more control about what duplicate you want to remove.

Conceptual Question: Keeping History of Updated Values in SQLite - store as separate table?

@qdmt glad you liked the previous hints. You could just setup a SQLite database that would collect all the entries where you can then select the latest one per Listing with the use of a Windows/Rank function (the current driver bundled with KNIME would allow this function). The latest results (per Listing) will then be stored in a new table my_tracking_table_02. [image] The key in this case is to set up the SQLite table so that it will have an automated PRIMARY KEY and an automated TIMESTAMP.…

[alex1368]

Write me a piece of code for a db query that reads my SQL table and deletes duplicate rows.
Preferably, the code should be such that there is no need to write the names of the columns
Explanation: I want to read the information code in the table line by line and delete it if there is a duplicate line.

[tobias.koetter]

@tobias.koetter

If you only want to eliminate the duplicates when retrieving the data but don’t want to do this permanently in the DB you can use the DISTINCT command e.g.

SELECT DISTINCT * FROM #table# AS "table"

in the DB Query node.

That should do the trick since it is independent of any column names and will only return the distinct rows.
Tobias

[yogesh_nawale]

Hi @alex1368,
May be you can try something like this.

For eg this is my table

Steps

1. Select table using DB Table Selector node
2. Using DB Query node find out the duplicates.
   This SQL query is designed to identify duplicate rows in a table based on the combination of the FirstName, LastName, and Department columns, and it returns only the duplicate rows (i.e., the rows with row_num != 1).

3. Use DB Delete(Table) to delete this duplicate rows

4. My output table

Delete duplicate information at the db level.knwf (81.1 KB)

See if it helps

Regards,
Yogesh

[alex1368]

thank you
Your answer was much more than I expected, thank you