Questions tagged [delete]

In the database structured query language (SQL), the DELETE statement removes one or more records from a table.

Filter by
Sorted by
Tagged with
0 votes
0 answers
17 views

Optimizing approx 1M row deletions on MySQL 8.0

I'm trying to delete in the order of 1M rows from a MySQL InnoDB table, with a schema similar to: CREATE TABLE `relations` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `relation_type_id` int ...
0 votes
2 answers
6k views

How to delete records without a primary key in a stardard way (i.e. not using ctid)

Case of use We have a log table without a PK with two columns (date, event); You inserted a row. Then you want to undo the inserted record. Is there a way to delete that row (without deleting ...
1 vote
0 answers
79 views

Why does a cascade delete cause such high logical reads?

I am doing some testing and have a simple setup. An order table linked to a customer table via a foreign key with On cascade delete. There is a unique nonclustered index on the (customer_id, order_id) ...
0 votes
1 answer
58 views

How to delete "SQL Server Agent" logs from "Log File Viewer"?

On "SQL Server 2019" on Windows 2019: I have opened Microsoft SQL Server Management Studio. I logged into database server. I clicked on "SQL Server Agent" and clicked on "...
0 votes
0 answers
80 views

Properly DELETE 500k+ Rows from Multiple Tables in a Single Script

There are several examples all over the Internet and StackOverflow on how to properly delete 500k+ rows from a single table and COMMIT those DELETES in Batches. I am unsure of how to incorporate these ...
0 votes
1 answer
677 views

resolve lock wait timeout delete innodb table

In an application I work with, DELETEs are rare, though they seldom happen. When they do, they remove few rows but from several related tables, like 8 to 10. Most of the times, mainly when the ...
5 votes
3 answers
643 views

How to minimize\plan ahead the effect of running sp_delete_backuphistory? Measure the gains too!

while running the following query: -- DATEADD (datepart , number , date ) DECLARE @dt DATETIME SELECT @dt = DATEADD(month,-6,getdate()) select @dt EXEC msdb.dbo.sp_delete_backuphistory @...
3 votes
3 answers
158 views

How can I know how many tables my DELETE has changed?

Suppose that I have many tables linked by a chain of ON DELETE CASCADE foreign keys. To the best of my knowledge and experience, deleting from one table gives me no notice at all that I have in fact ...
5 votes
1 answer
260 views

Merge Delete on joined tables is allowed but has a bug

To start off, an updatable CTE, derived table or view may be the target of an UPDATE directly, even if it has multiple base tables, as long as all columns are from the same source table. But they ...
3 votes
1 answer
524 views

Deadlock on DELETE - DELETE for parallel execution of same stored procedure

I have a stored procedure which ran parallely by two processes. Code for sp : DECLARE @inputXML XML DECLARE @tempTable TABLE (afid INT, ccid INT) INSERT INTO @tempTable SELECT ref.value('@afid', '...
2 votes
2 answers
755 views

CASCADE DELETE per statement

Can I execute something like this in Postgresql, that is, execute CASCADE DELETE for just one statement? DELETE CASCADE FROM foo WHERE bar = 'baz'; My goal is to delete not just the selected record(s)...
2 votes
2 answers
995 views

How to delete foreign key with lots of references

Table B have several thousand records that references table A with a foreign key. The application sends a request to delete the row from table A. It's important that the synchronous operation would ...
1 vote
1 answer
2k views

How to turn SELECT statement into a DELETE statement

I'm trying to turn this select statement into a delete statement and I'm struggling to not delete all the records in the table. Here is the SQL select statement I'm using SELECT * FROM ...
0 votes
1 answer
49 views

Move row of a table to an inherited table in PostgreSQL

I have tables created as such: BEGIN; CREATE TABLE tag ( id SERIAL, name VARCHAR(255) UNIQUE NOT NULL, description VARCHAR(511) NOT NULL DEFAULT '', PRIMARY KEY(id) )...
1 vote
0 answers
51 views

Huge difference in terms of duration between delete of columnstore and rowstore big table

We have sap table PRCD_ELEMENTS which is: Partitioned (on KNUMV column) Have PK aligned with partition schema Have non clustered index on rowid column which is aligned with partition schema Table has ...
1 vote
5 answers
10k views

Disadvantages to using ON DELETE CASCADE on every foreign key?

Our application currently has "soft delete" built-in to most tables with a BIT "deleted" column. There is now a requirement for "hard delete" functionality. Are there any ...
1 vote
4 answers
47k views

SQL Server : trigger FOR DELETE

I want to create a trigger, which does not delete a customer if his name is Peter. How can I do that? CREATE TRIGGER trgCUSTOMER ON customer FOR DELETE AS BEGIN IF (SELECT name FROM deleted) not ...
0 votes
0 answers
35 views

What is the risk of deleting millions rows from an offline table?

I'm a junior DBA for a medium-sized company that helps merchants exchange money during sales. Our services operate 24/7, 365 days a year, so performance and uptime is absolutely critical. We have a ...
21 votes
3 answers
139k views

MySQL - Delete row that has a foreign key constraint which reference to itself

I have a table in which I store all the forum messages posted by the users on my website. The messages hierarchy strucrue is implement using a Nested set model. The following is a simplified ...
5 votes
4 answers
8k views

MySQL DELETE statement doesn't use index although the same SELECT query does

I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following: id, cookie_id VARCHAR(45), country ...
1 vote
1 answer
135 views

SELECT / DELETE rows where consecutive null value count is greater than N

I have a table of the following structure, where all columns except the last are non-null: ID receiver send_time recv_time 1 A 00:00:00 00:00:01 2 A 00:00:01 NULL 3 A 00:00:02 NULL 4 A 00:00:03 ...
4 votes
1 answer
4k views

Can I enforce cascade delete in a delete from statement?

I am using constraints in a database meaning that records of a certain table cannot be removed lightly. That is normally the desired case. However, is there a way to write a DELETE FROM query so that ...
101 votes
7 answers
149k views

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
0 votes
4 answers
602 views

Keep the foreign key constraint using soft delete

I have a product table and it is foreign key for product ingredients table. Is there a way to check if it is fk referenced before making isdeleted = true without manually querying for it?
1 vote
1 answer
274 views

Postgres 11 log all statements not working as expected

I am trying to track down a row delete that is happening somewhere in a huge and sprawling application. I know that something deleted a row in an important DB table, and I have narrowed down when the ...
0 votes
2 answers
103 views

Delete all rows if input parameter is 0 otherwise delete only those entries which match the input param

I have a stored proc which is receiving id as input. If the id which is received is 0 then we need to delete all the rows otherwise we need to delete only those rows which matches the input id. I can ...
0 votes
1 answer
466 views

Delete parent without deleting children

I have three table: Products, Stocked products, sold products. Products and 'stocked products' are different tables because there are different batches of the same product stocked with different ...
0 votes
0 answers
83 views

Should I use soft delete as a record-disabling mechanism or better set another field to mark records as deactivated?

I'm making a web app with Laravel and PostgreSQL where many records in almost all tables in the database need to be kept by historical reasons (users need to search old data for statistical reasons). ...
6 votes
1 answer
3k views

Why is the 'DELETE' operation on a PostgreSQL database table unusually very slow?

I have encountered a significant performance issue when executing the 'DELETE' operation on a PostgreSQL database table. The execution time for deleting 15488 records was 79423.768 ms which is very ...
1 vote
1 answer
691 views

Do statistics have an impact on delete performance?

Background: I am a developer working on an application that has a relatively large table (in my experience at least) of 1.7 billion records. The table is a history table, and the application imports ...
1 vote
2 answers
102 views

How to leave only the rows with an timestamp difference with an interval greater than a parameter

I have a Postgres table with timestamps as entries. From this table, I would like to calculate a new one where there are no consecutive entries with a timestamp difference shorter than 400 ...
15 votes
3 answers
21k views

Is there a way to test whether DELETE will fail due to constraints?

I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete. What are my options for doing this? Is there a simple way to do a "...
5 votes
3 answers
2k views

Efficiently remove 90% of a table's data

I am working on a process to remove 90% of a table's data because only 10% is needed for testing. The best way I have found includes storing 10% of the table's rows into a temporary table. Current ...
1 vote
2 answers
323 views

Delete query takes a long time - table spool and hash match in query plan

After upgrading from SQL Server from 2012 to 2019 we have encountered problems with very simple delete query like: DELETE [_sales].[OfferDetail] WHERE [OfferDetailsID]=@1 Operation takes about 15-30 ...
1 vote
2 answers
621 views

Error with SQL DELETE statement with MariaDB

I have a multi-database application that has a query that is giving me execution problems with MariaDB 10.6.12 (Windows). The same query works perfectly when run against MYSQL 8.0.27 However, when it ...
15 votes
2 answers
7k views

Is the system column "ctid" legitimate for identifying rows to delete?

I have a table with hundreds of millions of rows that I need to delete data from. The existing indexes are the most efficient. I can however use the existing indexes to find the rows to delete by ...
0 votes
1 answer
49 views

Best BULK DELETE and Empty Space Recovery Options

What is the best option to recover empty space after running a bulk delete? DBCC Shrinkfile is useful to truncate the space. DBCC Shrinkdb is used to shrink the db files, but it leads to fragmentation....
47 votes
6 answers
57k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
1 vote
1 answer
77 views

Delete records in different tables

I need to delete the records where UserIndex = 1 and ItemNumber = 5202 in all three tables, all in a single query. I am using SQL 2008 R2. Table UserInfo1 UserIndex Itemnumber ItemCount 1 5202 99 1 ...
2 votes
0 answers
152 views

Purging large database tables

I know this has been asked few times in here, but my case is bit different. I would like to purge the data in from my SQL Server database of 700 GB and almost 1000 tables. I spent fair amount of time ...
1 vote
2 answers
181 views

Deletions getting omitted, data supposedly deleted returned in CQL queries

We are facing an issue on one of our production systems where after we delete the data the data doesn't seem to get deleted. We have a Get call just after the delete call. The data shows up. Versions ...
0 votes
0 answers
27 views

Would INNER JOIN on equality of two keys delete rows effectively?

Ī̲ repair a MediaWiki site after a DoS attack which injected terabytes of garbage into the text table. Ī̲ found that DELETE with clauses works painfully slow and, even worse, locks the table; that is, ...
3 votes
0 answers
224 views

Slower delete performance in MySQL8 compared to MySQL5.7

I'm looking for some insights into why deletes from a fairly simple table seem to be much slower in MySQL8 than in MySQL5.7 Apologies in advance if this is a little fluffy at this stage and I've ...
2 votes
2 answers
876 views

Why is MySQL Delete on Foreign Key Index locking a record not part of the index (leads to DEADLOCK)

I have a situation where I am executing a delete statement by a FK index that sometimes tries to lock a row that is not part of that index. I'm not sure why this could happen. Here's my scenario: ...
2 votes
2 answers
1k views

CTE vs. temp table for batch deletes

Two-part question here. We have a large table (between 1-2 million rows) with very frequent DML operations on it. During low volume periods, we have an agent job to remove older rows to keep the ...
1 vote
1 answer
655 views

Adding soft delete to a database after having used hard delete

Introduction My app collects data from a centralized source where many different users can submit data about their organisation and their staff. Previously we used to just hard delete a users data ...
12 votes
2 answers
60k views

deleting large volumes of rows from table efficiently in SQL Server [duplicate]

I have a table that has gotten a little out of control. I am not a DBA per se but seem to recall that deleting a huge volume of rows in one shot can cause transaction log issues, hamper overall system ...
10 votes
4 answers
15k views

Bulk Delete for Large Table in MySQL

I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month. If I do DELETE FROM NOTIFICATION WHERE CreatedAt &...
0 votes
1 answer
44 views

SQL Server Merge Replication bulk delete leaves subscriber databases large

We have SQL Server 2017 merge replication set up with three subscribers (running SQL Server 2017 Express). We need to clear data from the server database occasionally to reduce the database size at ...
2 votes
5 answers
17k views

Delete from table in chunks

I want to cleanup one of my tables which has 4 million. So I prepare this query to clean it. But it's throwing an error, can anyone please help me to fix this? DELIMITER $$ DROP PROCEDURE IF EXISTS ...

1
2 3 4 5
10