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
1answer
45 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 ...
0
votes
1answer
42 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 ...
1
vote
2answers
38 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 & ...
0
votes
2answers
24 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 ...
0
votes
1answer
24 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:...
0
votes
1answer
21 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 ...
0
votes
1answer
74 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 ...
0
votes
1answer
47 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)...
0
votes
0answers
42 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 ...
0
votes
0answers
81 views

Postgres jsonb field: remove an element from an array by the element id

Figure a Postgres 12.5 jsonb field containing the following: [ {id:1, name: 'one'}, {id:2, name: 'two'}, {id:3, name: 'three'} ] How to write the request that will match: "drop the object ...
0
votes
2answers
25 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 ...
1
vote
2answers
42 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 ...
0
votes
2answers
71 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
2answers
118 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 ...
0
votes
1answer
30 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. ...
0
votes
1answer
17 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' ...
0
votes
1answer
17 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 ...
0
votes
1answer
49 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 ...
0
votes
2answers
24 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 ...
5
votes
3answers
1k 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 ...
1
vote
2answers
798 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: +-------------------------...
1
vote
2answers
416 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 ...
1
vote
1answer
442 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, <...
3
votes
3answers
2k 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)...
0
votes
1answer
34 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: ...
1
vote
2answers
42 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 ...
1
vote
2answers
199 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 ...
0
votes
1answer
60 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 (...
3
votes
2answers
146 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 ...
1
vote
0answers
301 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 ...
0
votes
2answers
51 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 ...
0
votes
1answer
58 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 ...
0
votes
5answers
1k 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 ...
0
votes
0answers
593 views

Deleting child record doesn't delete foreign key reference

I am using PostgreSQL 10.7. I have this strange occurrence where I fail to see the problem. Whenever I delete all child-records referring to a parent, somehow the reference between these two tables ...
0
votes
1answer
279 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 ...
1
vote
1answer
45 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 ...
0
votes
1answer
238 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 ...
0
votes
0answers
21 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 ...
0
votes
1answer
73 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 ...
0
votes
1answer
78 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 ...
0
votes
1answer
38 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....
0
votes
3answers
53 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....
1
vote
1answer
48 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 ...
0
votes
0answers
26 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), ...
-2
votes
1answer
79 views

Oracle Delete From a View [duplicate]

I need to compile below query: delete from dba_audit_trail where TIMESTAMP > SYSDATE - 30;
-1
votes
1answer
754 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 ...
0
votes
0answers
38 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 ...
8
votes
4answers
2k 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 ...
0
votes
1answer
21 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 ...
0
votes
1answer
21 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 ...

1
2 3 4 5
9