How to remove duplicate entries from a large Oracle table (200M rows, 20 columns)? The below query from 2014 is slow. It took 2 minutes to delete 1 duplicate entry for one specific combination of columns (i.e. where col1 = 1 and .. col20 = ‘Z’). Any way to speed it up, e.g. with indexing? Answer R…
Tag: sql-delete
DELETE entry that is older than current datetime in MySQL
I have tried the following among various other statements but thought one of these should obviously work but no luck so far. Please tell me what I’m doing wrong. Not getting an error, it’s just not working. DELETE FROM table_name WHERE from < NOW() DELETE FROM table_name WHERE from < ‘…
Update to replace specific value with another except when it already exists
I have a table with values like this: There’s also a UNIQUE constraint on both columns I want to change all ‘poo’ to ‘woo’, as long as it doesn’t violate the constraint, and then delete the remaining ‘poo’, in order to obtain this table: My attempts to far for t…
T-SQL :: TRUNCATE or DELETE all tables in schema
I need to TRUNCATE or DELETE all tables in schema. I found this code: but on AdventureWorks it gives me: So I found this alternative code: But the result is the same: How to TRUNCATE or DELETE all tables in schema? Answer You simply need to wrap your “delete from all the tables” script with a R…
Query to delete record when no matching in delta table
please help to write a better delete query. DB2 does not have “not matched by source” with merge. Need to delete rows which does not have matching codes from source file for an ID and need to delete rows only for the ID’s present in source file. sample data row with value ID=1 & code=IJK…
Is there a way to include a DELETE FROM statement in a nested WITH statement?
I am trying to delete duplicates by creating another table but I am unable to include the delete from statement. I have the code here in SQL Server but I am trying to convert it to Oracle. I tried replacing the select with the delete from but I’m getting the error: missing SELECT keyword. Here is the co…
Deleting massive number of duplicate records without using a new table
Right now I have a table that has a massive number of duplicates that need to be deleted (about 500 million). I have a query that will delete all the duplicates, but it is unable to finish the whole query due to the transaction log being filled up. Moving the nonduplicates to a new table, then renaming it, wo…
Deleting records in MySQL WHERE id IN (@VARIABLE) — (2,3,4)
Is there is a way to delete records using WHERE IN @VARIABLE? Create variable: I’m trying to remove concatenated variables from string. The above SQL removes only first element from the list. In this example, list will contain: (2,3,4). Only the record with id = 2 will be removed. Records with id 3, 4 w…
Delete Rows which are fetched by a select query joining several tables
I have written the following select: What I want to do: I want to delete the rows in reminder, which are fetched by the described select. What I tried: Result: 00907. 00000 – “missing right parenthesis” I wonder if i can basically delete these rows in this manner. I also tried ‘exists&…
How to delete a row from a relationship table using two columns
I’m trying to delete a relationship between table T2 and T3 that is on table T1. I’ve searched but I could only find examples of a DELETE with only one INNER JOIN. I’m trying something like this, but I get a error: SQL error: ORA-00933. 00000 – “SQL command not properly endedR…