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
1 vote
1 answer
15 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
139 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
41 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
51 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
94 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
113 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
113 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
25 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
99 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
502 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
448 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
34 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
713 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
  • 11
0 votes
1 answer
784 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
341 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
1k 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
290 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
66 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
545 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
767 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
550 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
230 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
95 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
487 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
109 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
490 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
166 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
43 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
207 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
  • 399
0 votes
1 answer
333 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
1k 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
2 votes
1 answer
600 views

Copying Data into another table and removing older data should I do in batches and should I remove indexes and FK constraints?

I am looking to remove older data from a table which consists of 889 million rows of data. I have a script but I am trying to make it more robust as removing approximately 418 million rows of data to ...
rdbmsNoob's user avatar
  • 399
0 votes
1 answer
38 views

How properly create index for a referenced table

I have a table with thousands of records with vehicles data, and this table is referenced by the table, where images are stored for these vehicles. This second table about millions of records because ...
saby's user avatar
  • 105
2 votes
1 answer
2k views

Best practice for removing old data in postgres table but retaining a copy of removed data

We currently have a table that has approx 20 columns (one of the columns being a timestamptz datatype) in our database server which has 834 million rows. This in terms of size is a big table (approx ...
rdbmsNoob's user avatar
  • 399
0 votes
1 answer
146 views

Use trigger on DELETE to retrieve original SQL command and user who did it

I'm trying to create an audit log to store all delete commands on some tables, on MSSQL 2012. What I need is the username that executed it and the command executed, then I can insert them on a log ...
Hikari's user avatar
  • 1,493
1 vote
2 answers
130 views

Is Mysql creating any temp table , when running Mysql delete query?

Server details We have one Galera cluster Mariadb with 3 nodes . On primary node database size is 200GB & diskspace size is 400GB . On other 2 secondary nodes database size is 200GB & ...
Uday Raj Gupta's user avatar
0 votes
2 answers
355 views

Removing large amounts of rows from MySQL 5.7 table + ibdata increase question

I'm working with MySQL 5.7 on Windows. I've got several tables to clean data from. Largest table's ibd file is 300GB in size and it has almost 1.5 billion lines. I need to leave about 290 million rows ...
carrieandrsn's user avatar
0 votes
1 answer
302 views

Deleting records based on an offset from creation date

Basically, I have a table where I store messages for each user. Each user is allocated 1,000 messages, by 30 days. id user_id message created_at 1 12 ... 2020-01-01 16:30:45 2 45 ... 2020-01-02 16:...
DarkGhostHunter's user avatar
0 votes
1 answer
70 views

Dropping everything in a database owned by a certain role -- not working

I want to delete everything in a certain database owned by a certain role. I've tried to do and nothing has been deleted. sudo -u postgres psql postgres=# \l List ...
Nilcale's user avatar
0 votes
1 answer
889 views

Oracle: "table is mutating, trigger/function may not see it"

(Yes, this is one of those question again...) I need to support Oracle database in my software and am now facing the same problem than with MS SQL Server. I have a table I1 (called t_link in my ...
Matthieu's user avatar
  • 183
1 vote
1 answer
2k views

Delete large number of rows and change index - what steps in which order and things to watch out for

We daily collect data with multiple records per second. The table my_xpa_data has currently close to 100 Million rows and is growing fast. table size around 8 GB (used to be 4 GB a couple of days ago)...
surfmuggle's user avatar
0 votes
0 answers
287 views

MySQL mass delete from large innodb table very slow

I have a innodb table which contains more than 120million rows including duplicates. Let's name it as big_table. I fetch all the duplicates into another table(temp_big_table) which contains nearly ...
Madushan Perera's user avatar
0 votes
2 answers
214 views

Deleting Billion records in a range vs exact ID lookup MYSQL

I have a database table which is around 700GBs with 1 Billion rows, the data is approx 500 GB and index is 200GB, I am trying to delete all the data that's before 2021, Roughly around 298,970,576 rows ...
shekhar's user avatar
2 votes
2 answers
2k views

Get a real query plan for EXPLAIN DELETE

I have a delete query that runs a long time. I try to explain it: EXPLAIN DELETE FROM matches WHERE id = 1 And I get: QUERY PLAN Delete on matches (cost=0.43..2.65 rows=1 width=6) -> Index ...
Max Yankov's user avatar
0 votes
2 answers
319 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?
Abraham Mulugeta's user avatar
1 vote
2 answers
1k views

Deleting rows from child table without deleting from parent [closed]

I have 2 tables, connected with inheritance. I want to delete some rows from the child table. The deletion part works but it deletes rows from the parent as well: CREATE OR REPLACE FUNCTION ...
Dionisis Kotzaitsis's user avatar
0 votes
1 answer
95 views

What does the alias after DELETE mean?

What does the alias after DELETE mean? E.g. I have a query: DELETE p1 FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p2.Id < p1.Id What does it mean DELETE p1? I understand it as follows. ...
some1 here's user avatar
0 votes
1 answer
24 views

SQL which removes entries with duplicate emails

DELETE FROM Person WHERE Id IN ( SELECT Id FROM Person GROUP BY Email HAVING COUNT(*) > 1 AND Id <> MIN(Id) ) The above query produces the error: You can't specify target table 'Person' ...
some1 here's user avatar
0 votes
1 answer
467 views

POSTGRESQL: Using Values List in Delete

Based on the PostgreSQL Documentation it seems possible to use VALUES list as argument in Insert/Select/Update. Is it possible to use it as argument in DELETE something like: delete from some_table ...
Karlson's user avatar
  • 181
0 votes
1 answer
340 views

Run DELETE on each INSERT to enforce row limit

Using PostgreSQL 11. Consider a table like CREATE TABLE "logs" ( "id" INTEGER NOT NULL, "userId" INTEGER NOT NULL, "timestamp" TIMESTAMP ...
cis's user avatar
  • 359

1
2 3 4 5
9