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
1
vote
2answers
281 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
72 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
37 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
17 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
23 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
59 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
67 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
24 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
89 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
38 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
26 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
41 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
57 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
148 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
46 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
145 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
80 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
285 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
34 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
43 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
343 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
29 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
68 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
73 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
77 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
62 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
84 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
58 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
73 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
166 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
63 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
74 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
222 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
64 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
38 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 ...
2
votes
1answer
943 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
40 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
57 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
61 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
81 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
632 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
214 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
75 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
622 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
77 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 ...