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
29 views

Delete more than 50K records in SQL table

We have 5 Lacs 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 ...
0
votes
0answers
21 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
34 views

Oracle Delete From a View [duplicate]

I need to compile below query: delete from dba_audit_trail where TIMESTAMP > SYSDATE - 30; But it doesn't allow to delete from a view. I HAVE TO delete records from dba_audit_trail, I can't use ...
-1
votes
1answer
41 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
34 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 ...
7
votes
4answers
886 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
18 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
18 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
vote
0answers
22 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 ...
0
votes
0answers
18 views

Cannot delete from MariaDB federated table

In our web application, there are some federated tables which point to a remote server, it is used so that when writing log entries to the federated table they get actually stored on this remote ...
0
votes
1answer
34 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 ...
16
votes
2answers
3k views

SQL estimates are way off on DELETE statement with Triggers on huge tables

I'm working with Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) Nov 27 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 ...
0
votes
1answer
30 views

How to turn SELECT statement into a DELETE statement

I'm trying to turn this select statement into a delete statement and I'm struggling to not delete all the records in the table. Here is the SQL select statement I'm using SELECT * FROM ...
0
votes
1answer
26 views

Weird case , how do I delete a cluster under these circumstances [closed]

I would like to know how to delete a cluster with this type of case. To explain, I created a cluster for two nodes and there was a bug so I had to stop the service on the first node. However after ...
0
votes
1answer
28 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 ...
5
votes
2answers
1k views

Table size grows indefinitely after deleting and then inserting the same data

I create a table and populate it with data: create table tab as select 'value' || level val from dual connect by level <= 10000; If I then delete all rows and insert the same data: delete from ...
0
votes
0answers
66 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 ...
0
votes
0answers
29 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, ...
0
votes
0answers
28 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 ...
0
votes
1answer
12 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.
2
votes
1answer
107 views

Can I avoid TABLOCKX on DELETE?

Goal: Delete several billion records old records (roughly 600GB) across three tables with zero downtime. Methodology and Background: I plan to delete batches of records associated with 1 Million ...
0
votes
0answers
24 views

generating a log for Cascade deletes

I have a database which supports a .Net application and i have several parent tables and child tables database design where several tables are linked using FK and there is cascade delete enabled. So ...
1
vote
2answers
393 views

MySQL DELETE statement doesn't use index although the same SELECT query does

I've got a table with ~30 million rows (and soon twice/triple times more) where I have to do quite regular updates. The table structure is like the following: id, cookie_id VARCHAR(45), country ...
0
votes
1answer
50 views

Delete millions of rows based on ids in another table

I have two tables on MySQL: HISTORY_TABLE (4 million rows) (column1 indexed) EXTRACT_TABLE (600K rows) (column1 indexed) For every EXTRACT_TABLE.column1 there are multiple HISTORY_TABLE.column1 rows....
1
vote
1answer
62 views

SQL Server indexes and record deletion

I've inherited a large production database in which one of the tables has 72 million rows and is inserted/updated thousands of times during a day. These rows have built up over 3 years and they are ...
0
votes
1answer
77 views

Using of delete obsolete in Oracle 11gR2 RMAN on Linux

I'm using crontab for automatic backup (full backup). It works 2 times in a day (8 AM and 23 PM). In my script I use delete obsolete but I have a question. I want to delete old backups without last 2 ...
1
vote
1answer
259 views

How do I force the delete of users from my database?

I am making a clean backup of our development database for easy fresh spin-ups of the database. This database has about 200 test users that should be deleted to make it truly clean, but the problem is ...
0
votes
1answer
31 views

Delete comment - actually delete it, or create `deleted` column and update it?

I'm really not sure if this is the right community to ask it in, but it seemed like it fits here. But anyway, I'd like to know what's the usual approach of deleting a comment from database through ...
5
votes
2answers
737 views

Solving ON DELETE CASCADE cycles with triggers on MS SQL Server

I have code that is working fine in PostgreSQL and I now have to port it to MS SQL Server. It involves tables with potential cycles on delete/update events and SQL Server is complaining about it: -- ...
1
vote
1answer
59 views

Mongodb - Best Practice for TTL index on huge dataset

I want to create a TTL index for a table. Table size 1.2TB I have a shard infra. Each shard is having 3 node replica set. For creating this index, can I go with the rolling index creation method? ...
2
votes
1answer
174 views

Mongodb - Will Bulk.find.remove() lock the collection

I'm planning to run Bulk.find.remove() on a very big collection. (1.2TB). Will it block inserting or updating other records (which are not a part of the above find)?
-2
votes
1answer
42 views

ON DELETE / ON UPDATE doubts

I was looking at database past exam from the school that I attend, and I have a question about ON DELETE and ON UPDATE. The question in the past exam was: Show the contents of the tables after ...
0
votes
1answer
47 views

How to grant xp_delete_file to non-sysadmin login?

I want to delete old backups from shared folder, which are located on separate NAS storage, using newly created SQL login. Is there any workaround to execute xp_delete_file using another account which ...
6
votes
1answer
699 views

Deleting millions of records on SQL Server 14.0

I have two tables containing 200 Millions of records each. I have to delete from them, approximately 70 Millions of records, based on an integer value in a column. I'm deleting them in chunks of 4000,...
0
votes
2answers
100 views

Not able to delete transaction log backup file

I have a big SQL Server database (~1.5 TB) and i didn't take log backup of that database for last two weeks for some reason. Today i found that the .ldf file of that database became huge around 1.5 TB....
3
votes
3answers
928 views

tuning huge delete operation on sql server table

I am performing a delete operation on very large sql server table based on query as discussed below. delete db.st_table_1 where service_date between(select min(service_date) from stg_table) ...
1
vote
0answers
236 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 ...
1
vote
2answers
601 views

How to delete rows from relational database

So I have database with two tables. and they have foreign key relation between them. I want to do some testing but the tables are so big. I want to delete most of the data in these tables and just ...
5
votes
2answers
100 views

Best way to configure and maintain a table with many insertions and deletions that's empy on average?

There exists a "batch query" table that has two columns (BatchID, RecordID) which are both of TSQL type 'uniqueidentifier'. The unique clustering key is on (BatchID, RecordID), and there are no ...
1
vote
1answer
77 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 ...
1
vote
0answers
52 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 ...
0
votes
2answers
32 views

How to delete data from both tables or least one of them, when using “delete from … using … where” in Postgresql?

I have this sql script for my Postgresql db: DELETE FROM table1 USING table2 WHERE table2.frg_table1_id = table1.id AND table1.id = $1 I want to it to always delete data from table1 if the ...
1
vote
1answer
66 views

Trans Log fills during delete, conceptual question

I'm learning everything I can about backups and transaction logs and such. I want a little feedback on if I'm understanding this, so I have a conceptual question. First, here is what I observed: I ...
0
votes
1answer
114 views

Optimizing single-row DELETEs from a table with many FOREIGN KEY references

In a database I am working on, there is a table called persons with about one million rows, and 60 FK (FK) constraints from other tables (some with many millions rows) pointing to it. If I delete one ...
0
votes
0answers
27 views

Not in sync even after changing the Agent profile : Transaction Replication

Getting the transaction deadlock error,25098 and 547 in my replication monitor. I checked the subscribers and in one of subscribers ,i can see that ,the Distributor to Subscriber History shows error. ...
6
votes
1answer
106 views

Sql Server delete syntax

I recently faced a delete syntax I was unaware of. delete #fooTbl from #fooTbl where attr ='some' The Official Microsoft documentation states: DELETE FROM [database_name . [ schema ] . | schema. ] ...
0
votes
1answer
46 views

Deleting duplicate based on latest datestamp entry

I have a table and I'm trying to remove all the duplicate and keep the the rows that has the latest datestamp. Here is the table: email address orgin_date new_opt_in_date datestamp 123@...
0
votes
2answers
50 views

Delete old records based on its identifier

For example, I have a table similar to this: deviceid | servertime 1 2019-05-13 12:00:00 1 2019-05-14 12:00:00 2 2019-05-13 13:00:00 2 2019-05-12 13:00:00 2 ...
-1
votes
1answer
86 views

MySQL: Delete statement from complex select subquery

I would like to delete the output of the below SELECT command but I am not a DBA and my SQL knowledge is rather elementary. I want to delete the users matching the SELECT statement. SELECT count() ...
4
votes
1answer
110 views

Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql

In mysql 5.6, Consider these 2 examples creating relationships between A, B, C and D. Example 1 CREATE TABLE `a` ( id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE ...

1
2 3 4 5
8