Stack Exchange Network

Stack Exchange network consists of 175 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.

0
votes
2answers
23 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 ...
0
votes
1answer
32 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
49 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
83 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
42 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
139 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
46 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
140 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
31 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
37 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
176 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
26 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
65 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
66 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
58 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
60 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
69 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
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 ...
2
votes
1answer
57 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
70 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
157 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
28 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
61 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
46 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) ...
1
vote
2answers
131 views

MySQL - Delete with IN Clause taking more time

In MySQL 5.6 I have around 60 million 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
63 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
32 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
588 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
38 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
52 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
55 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
52 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
503 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
149 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
77 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
72 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 ...
5
votes
1answer
505 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
1k 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
62 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
295 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
123 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
87 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
35 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
1k 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
125 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
8k 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
217 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
48 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
970 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 ...