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.

54 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
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
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
0 answers
3k views

postgresql handle conflict on delete

I have a table that looks roughly like this: create table people ( id bigserial primary key, name varchar, deleted timestamp with time zone, parent bigint references people(id) ...
samf's user avatar
  • 31
3 votes
0 answers
2k views

MySQL delete gradually locks more and more rows

I've encountered a strange problem that I can't explain. MySQL 5.6.34, RDS. Basically, some delete from table where id in (...) queries are locking up indefinitely, but never truly deadlocking. I ...
Jafoy's user avatar
  • 83
3 votes
0 answers
518 views

Orphan records after delete statement in stored procedure

I have a database where some of the tables have parent --> child --> grandchild relationships through foreign keys as shown below. CREATE TABLE Parent ( ParentID INT UNSIGNED PRIMARY KEY ...
user3607758's user avatar
3 votes
0 answers
49 views

If a relationship is (logically) deleted, is this marked in the link table or just the satellite table(s)?

I'm learning about the Data Vault data warehousing approach, and so far the examples I've seen have a DateLoaded or similar field in all tables (hub, link, and satellite), but a DateExpired or EndDate ...
Jon of All Trades's user avatar
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
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 ...
CuriousGeorge's user avatar
2 votes
0 answers
529 views

Delete rows within a given time interval

I have created a table with the following values: create table MyTable (id int, dtdate datetime, serialNum int, serialCode int, status int); insert into MyTable values (1, 2012-12-20 01:01:00, 1, 10, ...
Tina's user avatar
  • 21
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
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 ...
adam.g's user avatar
  • 355
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
0 answers
128 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
0 answers
397 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
1 vote
0 answers
1k views

Manually delete AUTOBACKUP of controlfile

My boss has decided to change retention policy to a 31 days period instead of redundancy of 4 copies. One of the side effects is that autobackups get obsolete after 31 days. This is the configuration ...
outon's user avatar
  • 11
1 vote
0 answers
166 views

DELETE a single row from a table with CASCADE DELETE picks a slow plan... but not always

Schema Summary A dozen tables related by foreign key to a central table (call it TableA). All tables have a PK that is INT IDENTITY, and Clustered. All the tables' FKs are indexed. This looks like ...
Dave Horton's user avatar
1 vote
0 answers
2k views

Cannot truncate a table referenced in a foreign key constraint even with no child data?

With a ON DELETE RESTRICT restriction, I understand that you can't delete a row in the parent table if a reference exist in the child table, but is it expected behavior that even if the child table is ...
IMB's user avatar
  • 169
1 vote
0 answers
845 views

Zabbix on Postgresql - very slow deletion of event

Were using Zabbix for monitoring and I'm having problems understanding why deleting events is so slow. Zabbix: 4.2 (never mind the name of the db - it is 4.2) Postgresql: 10.8 nvps: ~400 hosts: ~600 ...
krej's user avatar
  • 11
1 vote
0 answers
635 views

How to enforce ON DELETE RESTRICT on MySQL?

This is my SQL code: -- DROP the database if it exists DROP DATABASE IF exists labtest; -- CREATE THE DATABASE CREATE DATABASE labtest; -- SHOW DATABASES; SHOW DATABASES; -- Use the database USE ...
user184383's user avatar
1 vote
0 answers
454 views

MS SQL, fastest way to delete 99% of rows from 100+ million rows table to deduplicate data

Due to an error in our application, one of the tables in our MS SQL has been flooded with duplicate rows differing only in PK identity column and timestamp +--------+---------------------+------------...
Ivan Koshelev's user avatar
1 vote
0 answers
42 views

Impact of numerous indexes on query optimizer for a delete statement

We have a 10GB table with 30GB indexes on it. The table consists of a few dozens of attributes a primary key on the id (varchar(32) type) and a dozen of indexes on anything but id key (except the ...
arthur's user avatar
  • 888
1 vote
0 answers
212 views

Cannot read sql statement into mysqlbinlog

Mysql 5.7 on Windows: show global variables like 'binlog_format'; using me the STATEMENT show master status; gives me log-bin.000053 Now I execute this: DELETE FROM test.emp WHERE EMPNO=109; ...
andrea's user avatar
  • 341
1 vote
0 answers
250 views

Why is tempdb consumed in this scenario? (MS SQL Server 2012)

I've found strange situation running data deletion script. This script seems to be correct, without side effect of consuming all space in TempDB. And other processes reported problem about ...
Max's user avatar
  • 111
1 vote
0 answers
99 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...
xeraphim's user avatar
  • 597
1 vote
0 answers
1k views

MySQL - Preventing lock wait timeout when deleting rows from self

I have a table of around 500k rows (not big data), that I need to query against itself and then delete some of the rows which don't match the filters. At the moment my query looks like this: DELETE ...
James's user avatar
  • 161
1 vote
0 answers
481 views

Why is this query stuck if there are no waits?

I am working with a vendor's activity logging system. One of the tables has 70+ million records and is constantly increasing at several hundred records per minute. They have a utility to delete old ...
SomeGuy's user avatar
  • 2,013
1 vote
0 answers
276 views

Importing large .sql file stops before even midway and starts deleting

Okay so i'm trying to import my minecraft servers .sql file into database and after first 1GB of importing data it stops without error and starts deleting what already has been imported any ideas? ...
epriih's user avatar
  • 11
0 votes
0 answers
15 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
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
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
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 ...
Hey_Mindo'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
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
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
0 votes
0 answers
844 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
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
718 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
0 votes
1 answer
84 views

Why do these queries deadlock and how to work this around in MariaDB?

The following 2 queries enounter DB deadlock in my program: DELETE FROM proxies WHERE NOT EXISTS ( SELECT 1 FROM proxy_xref_proxy_list AS pxpl WHERE pxpl.ProxyID=proxies.ID AND (pxpl....
Serge Rogatch's user avatar
0 votes
0 answers
111 views

Delete interlinked tables data using one query

I want to write the database query to delete the data from multiple interlinked tables. Below is the structure of the database: CREATE TABLE TABLE1 ( ID NUMBER(10), TABLE2_ID NUMBER(10), ...
Ashu's user avatar
  • 101
0 votes
0 answers
40 views

Review of scheduled bulk deletion on a production database

I have a EU privacy requirement to delete production data regularly. The script runs weekly when the traffic is quiet. I wondered if there is any way to improve this script. I can't delete directly ...
Rebecca's user avatar
  • 299
0 votes
1 answer
91 views

Transform SELECT query to DELETE to remove extra taxonomy entries

I try this query shows me the products that have duplicated, term_taxonomy_id in my web corresponds the stock of the product (none, medium, high, low) I would like to transform it into one query ...
beikone's user avatar
0 votes
1 answer
40 views

Delete all data for a Single Vendor in the Dabatase

I have a Vendor table with vendor_Id as the PK and 10 other tables which have reference to Vendor.vendor_Id. Now, if Vendor chooses to close their account, we need to delete all the data in the other ...
j10's user avatar
  • 289
0 votes
0 answers
270 views

Delete from table one row with 2 variable with limit

I'm developing a PHP script who store session of users in a Database. When an user logout from server, remove only one row ( because same user can login more than one time ) When server reboot, ...
rayjhp's user avatar
  • 1
0 votes
0 answers
168 views

What is correct way to ensure that row is not deleted in concurrent transaction

I have 2 tables: Parent (PK id) Child (PK id, parent_id) Note that parent_id is not defined a foreign key (actually they are not parent/child, I use those names just for example). I need to handle ...
GRaAL's user avatar
  • 121
0 votes
1 answer
86 views

How to delete all rows in a Greenplum row oriented table

In Greenplum, what is the best practice to delete all rows from a table? DELETE FROM TABLE or TRUNCATE TABLE? I dont have any child tables or foreign key constraints.
Siva's user avatar
  • 1
0 votes
0 answers
2k views

Unknown column "X" in field list on delete, but the column exists

I'm using MySQL and i'm trying to run a delete statement as follows: DELETE FROM `myTable` WHERE `nincidusua` = 84 and the following error occurs: Unknown column 'nincidusua' in 'field list' My ...
Rodolfo Donã Hosp's user avatar
0 votes
0 answers
120 views

Match and update records in a table

I have two tables table1: | id | A | B | C | date1 | date2 | |-----|------|-----|------|--------------|--------------| | 1 | 100 | 100 | 100 | 10-Jan-2019 | 11-Jan-2019 ...
mahesh gupta's user avatar
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 ...
Emilio Platzer's user avatar
0 votes
2 answers
163 views

Best way to delete large set of rows knowing field to NOT delete

Coming from a Rails background, I have a large production database that I have a copy of where I only need records from 4 of the 2000+ companies. I'm trying to delete all the rows except the ones ...
Tonys's user avatar
  • 131
0 votes
2 answers
111 views

After Exporting Database should be empty

I am exporting data from database for that I have written following query: SELECT ParameterID, Name,Type,Datatype,LogDate FROM Param_Ems WHERE DataType = 10 INTO OUTFILE 'C:/tmp/ParamData.csv' FIELDS ...
Sush's user avatar
  • 183