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
0answers
17 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.
1
vote
1answer
42 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
20 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 ...
0
votes
2answers
76 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
47 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
61 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
45 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
72 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
252 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
33 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
59 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
41 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
38 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
689 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
64 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
646 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
122 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
434 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
94 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
46 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
32 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
29 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
64 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
85 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
26 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
99 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
42 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
39 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
69 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
83 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 ...
0
votes
4answers
802 views

How to delete large amount of data in sql server without data loss?

I've been dealing with million of data deletion in day to day process. Basically I have 4 tables. Table_A Table_B Table_C Table_D I'm attempting to delete data older than 10 days in all tables. ...
1
vote
1answer
47 views

MySQL, operation DELETE

I have: DELETE FROM songs WHERE songs.id_album = album.id AND album.id_band =16` Error: #1054 - Unbekanntes Tabellenfeld 'album.id' in where clause. Table album exists, field id too, 100%. ...
2
votes
2answers
193 views

How to minimize\plan ahead the effect of running sp_delete_backuphistory? Measure the gains too!

while running the following query: -- DATEADD (datepart , number , date ) DECLARE @dt DATETIME SELECT @dt = DATEADD(month,-6,getdate()) select @dt EXEC msdb.dbo.sp_delete_backuphistory @...
0
votes
1answer
1k views

Deleting multiple tables in a single delete query in Mysql

I have a task to delete records from multiple tables (say 4 or more). I came to know that I can do the delete in a single MySQL query as follows DELETE table1,table2,table3,table4 FROM table1 ...
0
votes
0answers
418 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 ...
1
vote
2answers
119 views

How does ON DELETE CASCADE relate to JSON?

For relational databases, there is the notion of ON DELETE CASCADE. When working with JSON, how do these two concepts relate? Is ON DELETE CASCADE supported for JSON? Can you give an example of a ...
0
votes
0answers
44 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 ...
1
vote
1answer
1k views

SQLITE: Multiple foreign key referenced to multiple table cascade delete

In SQLite I would like to delete parent table row and it has to delete all the related child tables. I have gone through all the questions in StackExchange and other websites but my query no where ...
0
votes
1answer
31 views

How to delete all but the latest entry per customer in the fastest way?

I have a table containing around 4 million unique customers (with customerId each), but each customer can have multiple entries. There is no primary key. Each row has a creation dateTime saved as ...
1
vote
2answers
76 views

How to remove partial duplicates from a table

I have a database that contains some erroneous results that are essentially but not technically duplicates. The structure is like this: id_page (PK), id_site, label, create_date There should only be ...
2
votes
2answers
197 views

Does MSSQL log shipping automatically remove the backup files from both servers?

Does Microsoft SQL Server's log shipping feature remove the backup files automatically? I am concerned that the secondary server's hard drive will reach capacity unless the previously restored files ...
0
votes
3answers
142 views

Delete queries blocks other concurrent transactions

DELETE queries in my production SQL server are causing deadlocks. I know that DELETE is causing this because i checked the Extended Events and checked the deadlock XML and found out that this DELETE ...
1
vote
0answers
80 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 +--------+---------------------+------------...
0
votes
1answer
167 views

How to Remove Multiple Databases Marked with “Recovery Pending”

Recently I tried to clean my appilcations which used SQL database(s), therefore, the leftover databases are now marked with "Recovery Pending". Right now I can follow this to delete them manually. ...
-1
votes
1answer
28 views

Storing User Information after Deletion

I am building a collaboration platform that focuses on being a knowledge base for teams and a big part of creating a knowledge base is storing historical information even when an employee has left. ...
0
votes
1answer
25 views

Query between tables to delete similar records from another table

I am writing an SQL query on two tables products01 and products02 in a PostgreSQL database. For the entire set of records in products02 take the substring (first 10 characters of volumeId and use ...
2
votes
1answer
60 views

Which database transaction log will increase?

I found a query miss written in one of our databases like this Use database2 go delete database1.dbo.table1 from table1 as t1 join table2 on t1.column1 = t2.column2 and t2.column3 = 1 I am having a ...
2
votes
1answer
94 views

All data got deleted or truncated- What happened?

What are some of the ways of knowing how the 100 M rows of record table got wiped out. Database is in Full recovery mode. I am not sure how doing daily backups are happening but before the truncate or ...