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

Questions tagged [delete]

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

1
vote
2answers
46 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
48 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
2answers
48 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
52 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
42 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
27 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
23 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 ...
0
votes
0answers
44 views

When would exclusive lock escalate from row level to table level when I delete data from my table?

I have SQL Server 2012 (SP3-CU10) and a table, that has - 10 million rows - 11 Gb in size (6 GB data + 5 Gb index) - 20 foreign keys from 20 other tables are referencing this target table's ID column ...
2
votes
1answer
54 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
60 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 ...
2
votes
1answer
90 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
55 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
24 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
34 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
61 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
23 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
102 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
30 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
2answers
45 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
51 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
32 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
179 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
58 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
75 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
49 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
244 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
812 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
48 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
124 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
91 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
59 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
33 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
360 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
100 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
4k 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
106 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
47 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
596 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
683 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
410 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
1k 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
515 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
898 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
3k 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
143 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
122 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
619 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
58 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
106 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 ...