Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

In the database structured query language (SQL), the DELETE statement removes one or more records from a table.

2
votes
1answer
53 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
59 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 ...
-1
votes
0answers
30 views

MSSQL All table data gone

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 ...
2
votes
1answer
72 views

SQL Server - Remove all logins that they are asossiated with orphan users

I have an SQL Server 2008R2 that had lots of databases. The procedure was that a logins were created and users were mapped to databases. Most of these databases have been removed from the server ...
0
votes
1answer
27 views

Repeatedly emptying a table, keeping the table itself [closed]

I have been using "delete from table_xyz" to delete everything in the table. Would it be better to instead use truncate? What difference does it make?
2
votes
2answers
53 views

Delete one in two rows in an ordered result set

Is there a way to delete one in two rows in a result set using a single query? E.g. How to turn: +-------------+--------+-------+-------------+ | name | gender | state | date | +------...
0
votes
1answer
21 views

Delete all records in tables with reference to another reference

Sorry if the title to this question makes no sense. I really wasn't sure how else to word what I needed to do. So I have the following tables in my database: customers customer_id (PK) ...
0
votes
2answers
28 views

MySQL - Delete with IN Clause taking more time

In MySQL 5.6 I have around 6 crores of rows. I wanted to remove rows based on Id. Here Id is the primary key. One strange behavior I had today. delete from tbl where id=1; Its executed in ...
0
votes
2answers
60 views

Deadlock when delete using OR operator

I got a deadlock when having more than one sessions doing the following: start transaction insert data to table A delete data from table A commit at the same time They may delete the same records ...
0
votes
1answer
21 views

What happens with a synonym if the table which its based on gets deleted?

we are working on a project to implement a large ERP system. We found out that one of the tables has a synonym to another database. Both databases are in the same instance. Our project leader has ...
1
vote
1answer
51 views

Insert Trigger to a Linked Server error

I created an insert trigger in a table that will fire when an insert/update/delete is made. The trigger is supposed to send information about the query that fired it, but every time it fires, the ...
1
vote
1answer
29 views

Delete from table using FK columns, JOIN and LIMIT

This query below will never complete, since it always looks at the first 100,000 (user_type, user_ids). How can I write this to delete 100,000 records from the table with the FK reference (Product) to ...
0
votes
0answers
22 views

Excluding tables while cascade deleting

How can I exclude some tables while cascade deleting? Suppose I have a table A which is referenced in table B, table C and table D. On cascade delete I want to delete records in table B and table C ...
0
votes
2answers
43 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 ...
3
votes
1answer
45 views

Delete Joined to Derived Table Causes Index Scan and Table Spool

Our application uses a popular 3rd-party extension to Entity Framework that allows for valuable things that native Entity Framework is not capable of or designed to do, like bulk deletes and updates. ...
1
vote
0answers
26 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) ...
0
votes
1answer
105 views

Postgres taking 3h to DELETE CASCADE

I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 ...
2
votes
3answers
50 views

Large update and delete based on another table

I have a very active PostgreSQL 10.4 DB, dealt with through SQLAlchemy, so SQL is not my forte. Among its tables, there are master with a primary key id and some nonindexed string column value, and ...
0
votes
3answers
74 views

when delete data from big table led the db can't access

when use delete from statement to delete data month by month to clean old data,found it runs slower and slower,and couples of hours later,the DB can not access,and with such error msg as pic shows. ...
2
votes
1answer
44 views

Is postgres vacuum secure

Postgres delete doesn't actually remove the record from the table file. Once the delete is committed and the last transaction is closed, no postgres process can see the record any more, the deleted ...
3
votes
1answer
149 views

Is the system column “ctid” legitimate for identifying rows to delete?

I have a table with hundreds of millions of rows that I need to delete data from. The existing indexes are the most efficient. I can however use the existing indexes to find the rows to delete by ...
4
votes
3answers
762 views

Stored Procedure Asking for Input While Processing

So I am asking this to see if this is something that is even possible within SQL. I am currently writing up some stored procedures for a VB.Net application I am working on to use to interact with the ...
0
votes
2answers
39 views

Best approach to delete and update a big amout of rows in MySQL

I'm currently developing a REST API that connects to a MySQL database and does a bunch of queries. In one of those queries I happen to have to delete a bunch of rows from a table and each of those ...
2
votes
2answers
101 views

Delete records if not present in subquery

I want to delete records from a table that are not found in a subquery. But it's not having the desired result. The table has multiple rows per SKU. Each price row has a valid_from and valid_to date ...
1
vote
2answers
84 views

Deleting old data in MySQL becomes slower over time

I have an application running on Amazon MySQL/RDS that needs to maintain 2 weeks of transactional history after which time data can (usually) be purged. Because this isn't a hard rule I cannot use ...
2
votes
0answers
51 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, ...
1
vote
0answers
32 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 ...
-2
votes
4answers
233 views

Delete multiple rows based on a choice between a combination of two columns

I'm trying to write query do delete rows based on two columns: DELETE FROM us_TEST WHERE [cell] in ('CA001018611', 'CA001135126') AND [date] IN ('2016-01-04', '2016-07-14') However this ...
1
vote
2answers
94 views

Postgres 10.3 : table grows in size after delete-insert

I'm a rather new to Postgres and the issue I see seems a bit strange. Maybe you as experts know something about it. I have a table (around 3 million rows and 200 columns). In order to populate fresh ...
1
vote
2answers
2k views

How do I uninstall the old version of Postgres on CentOS 7?

I just installed Postgres 9.6 on CentOS 7. However when I run [root@server tmp]# /usr/bin/psql --version psql (PostgreSQL) 9.2.23 It still seems to be pointing to my old version. How do I ...
2
votes
0answers
72 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 ...
1
vote
1answer
44 views

Does the table constraint CHECK gets executed on deletions?

I guess not but if someone has an idea perhaps could give me a hand. I'm trying to restrict the deletion of a row of a table under some circumstances that requires to query against other tables. An ...
4
votes
3answers
456 views

Delete data from a billion row table based on where clause

I need to delete about 400 million rows from a 1.3 billion row table based on an indexed date field. The table is about 800 GB in size. What is the most efficient way to delete the data without ...
5
votes
2answers
677 views

Keep ROW = NULL or DELETE it?

in this scheme id uid content 1 1 A 2 1 B 3 1 C 4 1 D 5 1 E If the User decided that he doesn't need the rows with id = 3, 4, 5 Is it better to make him ...
1
vote
1answer
323 views

Tables with mixed ON DELETE CASCADE and ON DELETE RESTRICT, rule set with recursion?

Not sure if this is a generic question, but unless not, this is MySQL 5.6. We have a database consisting of ~300 tables, INNODB, all connected with proper foreign keys. One of these tables can be ...
2
votes
3answers
929 views

delete query takes forever

I got a reasonable simple query: With RowsToDelete AS ( SELECT TOP 500 Id FROM ErrorReports WHERE IncidentId = 611 ) DELETE FROM RowsToDelete However, it do not complete. I have tried ...
1
vote
1answer
424 views

Can I enforce cascade delete in a delete from statement?

I am using constraints in a database meaning that records of a certain table cannot be removed lightly. That is normally the desired case. However, is there a way to write a DELETE FROM query so that ...
0
votes
0answers
629 views

Oracle: insert /*+ parallel */ and delete /*+ parallel */ freeze during execution

I'm struggling with an issue with two queries executed on an Oracle DB. Both queries have the /*+ parallel */ directive. The two queries are executed in sequence: before I have the delete /* + ...
5
votes
3answers
2k views

Delete query not deleting data

I am running a web service which connects to a SQL Server 2012 database. When executing multiple delete queries within a short space of time (around 5 in a second), it appears random which ones are ...
1
vote
2answers
125 views

Count even deleted rows

Is it possible to count even deleted rows in a SQL Server table? Let's say we have a table Contacts. I'll add 5 new contacts, and hit count on it. It should return value of 5. Now, when I delete a ...
1
vote
1answer
99 views

DELETE … IN hangs after deleting half of a large table (even for deleting 10 rows)

I'm having trouble with a DELETE hanging, even for a small number of rows (10) in a large MariaDB InnoDB table. Background I had a large table (~400,000,000 rows) that needed to have rows archived ...
5
votes
2answers
525 views

Best approach to delete large table with foreign key constraints

How to purge a table with a billion records that has foreign key to another table? Should one partition the table and the child table and use partition swap?
1
vote
1answer
57 views

Data Scrub - Duplicate Records

I wanted to delete duplicate records in my database, but it's a little more in depth than just one field. Status ID P 150013511822029 P 150022555544009 P 150022551512029 X 150014520920007 R ...
3
votes
0answers
99 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 ...
0
votes
0answers
107 views

MySQL 5.7 Using and Truncating Partitions

I am working on a large statistical database. It's currently several hundred million rows. What I need to do each night is to truncate it and then rebuild the data from the source files because ...
1
vote
2answers
847 views

Delete databases from oracle 12c

I want to delete multiple databases and its entries. These instances are not getting started. I want to delete data files also. EDIT: I am thinking delete all data files. Please help on below concern....
2
votes
3answers
131 views

Why does my DELETE command require huge amount of temporary run storage?

I am trying to perform a cleanup operation on a table using DELETE and I receive the following error: Could not allocate space for object 'dbo.SORT temporary run storage: 140767697436672' in ...
7
votes
3answers
3k views

Delete millions of rows from a SQL table

I have to delete 16+ millions records from a 221+ million row table and it is going extremely slowly. I appreciate if you share suggestions to make code below faster: SET TRANSACTION ISOLATION LEVEL ...
1
vote
3answers
1k views

MySQL - Delete table in chunks

I want to cleanup one of my tables which has 4 million. So I prepare this query to clean it. But it's throwing an error, can anyone please help me to fix this? DELIMITER $$ DROP PROCEDURE IF EXISTS ...
3
votes
1answer
107 views

Unused Memory Grant on Delete

I have this delete statement delete from dbo.HighSchoolGradeLevel where Id=@P0 This is the execution plan https://www.brentozar.com/pastetheplan/?id=H1qzvl3nW when the application is busy and ...