Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
21 views

Upon PC restart, MySQL service on local computer "started and then stopped"

I'm working in MySQL on a local (Windows) computer. I have a somewhat recurring issue I have with managing the server. I cannot replicate this error because I do not know what causes it. Sometimes, I'...
1 vote
1 answer
2k views

Optimizing single-row DELETEs from a table with many FOREIGN KEY references

In a database I am working on, there is a table called persons with about one million rows, and 60 FK (FK) constraints from other tables (some with many millions rows) pointing to it. If I delete one ...
2 votes
2 answers
1k 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 ...
3 votes
1 answer
536 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', '...
1 vote
3 answers
85 views

Decreasing execution time of insert and deletes in SQL Server

I'm looking for some advice on reducing the execution time for inserts and deletes on a custom database setup that is used for logging data from a remote location. Basically I have a custom program ...
0 votes
0 answers
22 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
82 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
89 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
85 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
720 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
647 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
165 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
264 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 ...
2 votes
2 answers
1k 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)...
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
52 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
54 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 ...
2 votes
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
36 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
140k 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
153 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 ...
103 votes
7 answers
151k 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
676 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
385 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
122 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
580 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
87 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
712 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
105 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
350 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
669 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 ...
16 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
58k 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
153 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
195 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
28 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
234 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
908 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
666 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 ...

1
2 3 4 5
10