Skip to main content

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
3 votes
1 answer
1k 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
  • 459
0 votes
1 answer
76 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
4k 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
  • 459
0 votes
1 answer
427 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,553
1 vote
2 answers
184 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
607 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
627 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
161 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
1k 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
  • 233
1 vote
1 answer
3k 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
1 vote
0 answers
398 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
590 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
4 votes
2 answers
4k 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
4 answers
742 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
2k 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
134 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
33 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
999 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
921 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
  • 469
0 votes
2 answers
70 views

Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014

We are given 5 tables. Orders Order number (Primary Key) Date of order Customer ID (Primary Key for Customers table) Serial Number (Primary Key for Machines table) Customers ID ...
Donald Alesson's user avatar
10 votes
3 answers
19k views

Delete rows older than x days without locking table

we have a quite big MS SQL database with millions of rows. I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application. The ...
user2992455's user avatar
6 votes
2 answers
13k views

MySQL: DELETE didn't shrink DB size, nor table size

In mydatabase, there is a table table1 with almost 8M row, after deleting around 7M rows from the table, the size of the DB and the table itself remains the same. Table size: +-------------------------...
Eng7's user avatar
  • 229
3 votes
2 answers
4k views

PostgresQL Delete Trigger does not delete row

I'm currently creating archive records for my database, where each table x has a corresponding x_archive table. My solution was to create a trigger for each table that needed replication that would ...
jec's user avatar
  • 33
1 vote
1 answer
2k views

PostgreSQL: Delete Multiple Rows with multiple Conditions from the same table

I have a dilemma that makes for sort of curious experiment to be conducted but I was wondering if it already has been done. I have a table: create table test(action_date date, item_num int, <...
Karlson's user avatar
  • 181
3 votes
3 answers
17k views

How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?

The below is a valid TSQL statement. But I want to understand how SQL Server processes it. DELETE A FROM table1 WITH (NOLOCK) WHERE EXISTS ( SELECT 1 FROM table2 B WITH (NOLOCK)...
a_shie's user avatar
  • 31
0 votes
1 answer
784 views

Postgres SQL: INSERT from DELETE

I have a table with id,user_id,timestamp and I'm trying to INSERT into the table, the user_id from a DELETE from table where id=some_number and timestamp. So what I'm trying to achieve looks like: ...
Ghanashyam Sateesh's user avatar
1 vote
2 answers
89 views

How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014?

For example, I have a table of: id | code | name | type | deviceType ---+------+------+------+----------- 1 | 23 | xyz | 0 | web 2 | 23 | xyz | 0 | mobile 3 | 24 | xyzc | 0 | web 4 ...
SirFaps's user avatar
  • 11
1 vote
2 answers
2k views

How can I efficiently delete records when a counter reaches zero in PostgreSQL?

Consider a record with a counter field, which is to be decremented. When its value reaches zero (which is the common case), I want the record to be deleted. What is the most efficient way to do this ...
Diomidis Spinellis's user avatar
0 votes
1 answer
230 views

PSQL cte delete seem have have ambiguity when select same column from delete table

Both these two sql can be execute , the first one delete all , the second one is logic right. the first one with d0 as ( delete from seller_create_request where seller_id in (...
jiamo's user avatar
  • 139
3 votes
2 answers
374 views

Extremely bloated transaction log during delete

Now, I know that the log can get large during big deletes and one should try to chop it into batches. But I find this scenario strange and if someone could explain it to me that would be GREAT!. I ...
Bergmeister'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
0 votes
2 answers
248 views

Deleting all the content from the database

We want to clean a database (delete all the records) with around 25 tables and a size of around 650+ GB in production. We are currently using MS SQL 2016 Standard Edition with AlwaysON HA. Is ...
Mrigank's user avatar
  • 43
0 votes
1 answer
123 views

Logical Backup strategy in large Oracle Databases

in Oracle databases, I would like to know if it is advisable to make logical backups with exp or expdp in large databases with large volumes of data, greater than terabyte, I have seen several systems ...
miguel ramires's user avatar
2 votes
5 answers
11k 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 ...
Danny Beckett's user avatar
0 votes
1 answer
2k views

Delete rows on a table with cascading foreign keys

I want to delete rows on Table A but it has foreign keys on Table B and Table D. And Table B has foreign keys in Table E and Table D has foreign key in table F. Can I delete all the cascading records ...
Emmanuel Libradilla's user avatar
1 vote
1 answer
470 views

Delete duplicate records and update the mapping table

I have store_table_map and item_price_map. I want to delete duplicate records in store_table_map and retain the latest row based on updated_date because i want to put constraints in store_id and ...
Emmanuel Libradilla's user avatar
0 votes
1 answer
2k views

after deleting 100 millions of records in postgresql free space doesn't change

Recently I configured netflow for monitoring traffic of my company. I use pmacct to collect netflow packets and PostgreSQL for storing them. I only keep netflow records for a week and after that every ...
ali.n's user avatar
  • 1
0 votes
0 answers
22 views

PostgreSQL - Simple deletes takes more time [duplicate]

Today I found a weird(may not be) thing in PostgreSQL 12. Im trying to delete rows from a table with where condition from another. Both are small tables. But the delete is going more than an hour but ...
TheDataGuy's user avatar
  • 1,950
0 votes
1 answer
395 views

Recommendations about deleting large set of rows in MSSQL [closed]

I need to delete about 75+ million rows from a table everyday that contains around 3.5 billions of record. Database recovery mode is simple, I have writen a code that deletes 15.000 rows in a while ...
helloEveryone's user avatar
0 votes
1 answer
831 views

SQL delete where not in (...) query not behaving as expected

We have the following query which returns the most recent fares from our database : SELECT f1.id FROM ( SELECT * FROM fares ) AS f1 INNER JOIN ( SELECT origin_id , destination_id ...
Milan's user avatar
  • 101
0 votes
1 answer
85 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
3 answers
135 views

What is wrong with this syntax of a multi-table DELETE...ORDER BY?

I have the following query: DELETE proxies, pxpl FROM proxies JOIN proxy_xref_proxy_list AS pxpl ON proxies.ID=pxpl.ProxyID WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR) ORDER BY pxpl....
Serge Rogatch's user avatar
1 vote
1 answer
287 views

Delete more than 50K records in SQL table

We have 500k records in a SQL table. Below is table structure UserID varchar FirstName varchar LastName varchar Address varchar We need to delete 50K records in this table. We tried below approach ...
Sukhjeevan's user avatar
0 votes
0 answers
114 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
-2 votes
1 answer
269 views

Oracle Delete From a View [duplicate]

I need to compile below query: delete from dba_audit_trail where TIMESTAMP > SYSDATE - 30;
jrdba's user avatar
  • 55
-1 votes
1 answer
2k views

How To Delete Record From dba_audit_trail in Oracle 11gR2

I want to delete records older than one month from AUD$ table, but I have to do this on dba_audit_trail, because I have to do this job according dba_audit_trail's timestamp. I can't find any clear ...
jrdba's user avatar
  • 55
0 votes
0 answers
41 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
8 votes
4 answers
3k views

Delete statement not responding to Kill session command

Four days ago a user ran the command below on a table with 400,000,000 rows. It's still running and the log file is increasing in size. delete from [table-name] This table does have a foreign key ...
David Woakes's user avatar
0 votes
1 answer
151 views

Removing AG and clustering - make nodes stand-alone

I am wanting to make a 2-node Always On cluster no longer a cluster (make them (2) stand-alone machines). I am trying to upgrade Sql and the wizard keeps getting stopped before it does it checks ...
rvsc48's user avatar
  • 553
0 votes
1 answer
81 views

Is there an easy way to delete all records in multiple tables (but not every table)?

So I "fixed" an issue the other day, which has caused a snowball of new issues (imagine that). I have a large group of tables that serve as Temp tables for many import/export processes that flow to ...
lFreightTrain's user avatar

1
2
3 4 5
10