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
26 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'...
ZZ Top's user avatar
  • 1
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 ...
Big Bill's user avatar
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 ...
Santiago Árraga's user avatar
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) ...
Backend's user avatar
  • 11
0 votes
1 answer
91 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 "...
folow's user avatar
  • 339
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 ...
Code Novice's user avatar
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 ...
J. Mini's user avatar
  • 433
5 votes
1 answer
265 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 ...
Charlieface's user avatar
  • 13.3k
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) )...
SK19's user avatar
  • 101
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)...
Timur Shtatland's user avatar
1 vote
0 answers
55 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 ...
adam.g's user avatar
  • 355
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 ...
Hey_Mindo's user avatar
1 vote
1 answer
154 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 ...
Edgxxar's user avatar
  • 13
1 vote
1 answer
400 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 ...
chrestomanci's user avatar
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 ...
Himanshuman's user avatar
0 votes
1 answer
589 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 ...
Azzedine Bk's user avatar
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). ...
Dovahkiin's user avatar
1 vote
1 answer
713 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 ...
ldam's user avatar
  • 113
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 ...
ShwetaJ's user avatar
  • 163
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 ...
eddie_jung's user avatar
1 vote
2 answers
670 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 ...
Marcello Picchi's user avatar
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....
rushabh138's user avatar
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 ...
Ichigo Kurosaki's user avatar
1 vote
2 answers
352 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 ...
rotman's user avatar
  • 111
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 ...
Anand Shrestha's user avatar
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 ...
gudge's user avatar
  • 133
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, ...
Incnis Mrsi's user avatar
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 ...
Neil Database's user avatar
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 ...
DBA Greg14's user avatar
2 votes
2 answers
909 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: ...
Chris Landry's user avatar
0 votes
1 answer
50 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 ...
Steve Colley's user avatar
0 votes
1 answer
2k views

Routinely deleting data from tables using stored procedure

I've created a maintenance stored procedure to delete data older than 30 days on events table. What are the best practices for deleting data? I've encased the procedure with error handling, i.e. TRY......
dexon's user avatar
  • 47
0 votes
1 answer
1k views

postgresql - delete duplicate object in json

I need to remove duplicated objects in a jsonb field in table in PostgreSQL 12.7. How can I do this for all rows in table? In this example I need to remove the 3th and 4th object (date: 2022-09-12) [ ...
Damiano Dotto's user avatar
1 vote
1 answer
825 views

Can someone explain in simple terms Cassandra nodetool commands like compact and cleanup?

Hello Everyone I am new in databases and i am trying to understand some Cassandra commands. I would like to know about the following commands : nodetool compact nodetool cleanup nodetool repair ...
Vasilis_Dimitrakopoulos's user avatar
0 votes
2 answers
2k views

Restrict delete statement on certain table in postgres?

I have a problem where my customers are complaining that records get deleted even though I see all id's are correct in that table, just to make sure everything is like it should be, is there a way to ...
Pietro Sulizi's user avatar
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', '...
Himanshuman's user avatar
-3 votes
1 answer
109 views

DELETE ... INTO ... FROM (SELECT)

I am wondering why this is coded this way. declare @messages table ([SEQUENCE_ID] BIGINT, [MESSAGE_ID] BIGINT PRIMARY KEY CLUSTERED ([SEQUENCE_ID])) ; delete T output deleted....
smartwombat's user avatar
1 vote
2 answers
1k views

Delete but keep x amount of rows per input

Let's say I have the following table, with data: Id User Item Time 1 1 Banana D+1 2 1 Banana D+2 3 1 Banana D+3 4 2 Apple D+1 5 1 Apple D+2 6 2 Apple D+3 7 2 Apple D+4 The column definitions ...
Robin Jonsson's user avatar
0 votes
1 answer
1k views

Trigger execution order after delete

Let's say I have two tables, one called ParentTable, and another called ChildTable, with a one-to-many relationship, where one Parent may have many Children. I'm creating an AFTER DELETE trigger on ...
CarenRose's user avatar
  • 139
0 votes
0 answers
848 views

Best way to manage old data in postgres

I have a postgres db where everyday a significant number of rows across multiple tables are considered outdated and soft deleted. My understanding is that there's no point in keeping these rows around ...
Luke Vella's user avatar
-3 votes
1 answer
364 views

Permanently Delete the sql server database table

I wanted the remove the table from the sql server database. The table has 500 million records. I don't need the table anymore. What's the best approach? Can I use drop table command to delete the ...
anu's user avatar
  • 57
1 vote
0 answers
129 views

Archiving Historical Data

Right now on Production, for deleting historical data, used a script which is manually run in SSMS. Script deletes batches of 100 rows from Table1 where [Date] between [Start] and [End] in a looped ...
Aleksey Vitsko's user avatar
1 vote
1 answer
670 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 ...
Mikkel's user avatar
  • 21
0 votes
2 answers
202 views

Oracle parent child move failed

I have two tables, a parent and child table, which have referential integrity declared between two tables and try to move both the tables so I can delete rows. I start with the child table and the ...
Pugzly's user avatar
  • 105
0 votes
1 answer
901 views

How to implement insert, update, delete with a many-to-many relationship for tags?

I am creating an application to manage some ebooks and I would like to have tagging functionality. The best db design I have found is to be the many-to-many relationship. I know how to set up my ...
Attitude12136's user avatar
0 votes
1 answer
239 views

MSSQL Large table rolling using partitions

I have a large DB (6TB) consisting of 3 tables: “messages”, “messagesDetails” , “unprocessedMessages”. Here are simple descriptions of them: TABLE [messages]( [id] [bigint] IDENTITY(1,1) NOT ...
Piotr Grudzień's user avatar
0 votes
0 answers
49 views

Actualizar en Trigger de delete en Mysql8

tengo una tabla en MySQL8 y una situación similar a esta: Tengo solucionado el trigger de insert, pero al querer hacer el trigger de delete (por ejemplo) me dice que no puedo modificar la tabla ya ...
Franco Cuello's user avatar
0 votes
1 answer
287 views

What postgresql.conf values in Postgres 9.6 would help being altered for Large Data Deletion/Migration

I have a query which is to remove approx 418 million rows from a table into a separate table until we decide what to do with the older data. Query below being used: BEGIN; CREATE TABLE public....
rdbmsNoob's user avatar
  • 459
0 votes
1 answer
721 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 ...
watery's user avatar
  • 386
1 vote
2 answers
3k views

Delete old rows from a big table, keeping one per given time interval

I am trying to delete some rows from a table in which I have saved timestamps only (data type timestamptz): The usual operation to delete from DB/table is very simple such as I want to delete all ...
Junaid Farooq's user avatar

1
2 3 4 5
10