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

learn more… | top users | synonyms (1)

3
votes
2answers
2k views

Oracle: deleting data from tables leads to data file fragmentation?

When randomly deleting ~5GB from a ~10GB Oracle table, I suspect that disk space is left in fragments. Is there a way to know about the disk state and any query to defragment or shrink the space?
3
votes
2answers
167 views

SQL Server 2008 R2 - Unable to delete Login

I've been using this site for a while and this is the first time I've had to ask a question, but just wanted to say "Thank You" to everyone who has helped me in the past (without knowing they did so). ...
3
votes
3answers
1k views

Bulk Delete for Large Table in MySQL

I have a Notification table contains about 100 million rows host in Amazon RDS with 1000 IOPS, and I want to delete those rows older than one month. If I do DELETE FROM NOTIFICATION WHERE CreatedAt ...
3
votes
1answer
624 views

Copy database and clear down data (including linked tables) older than N days

I have a sizable production database (140GB) on MS SQL Server 2012, which I'm trying to automatically download to our development environments on a periodical basis (every week or on demand). I'm not ...
3
votes
1answer
180 views

MySQL trigger after insert with insert and delete instructions does not work properly

I have the following trigger created in phpMyAdmin: Table: tb_agenda Time: AFTER Event: INSERT BEGIN INSERT INTO tb_realizacao (dt_agenda, titulo, ...
3
votes
1answer
118 views

How to index for timestamp queries

I have a products table where I insert around 150,000 records a day. Most of them are redundant, but I need to keep them because of the new expiration date. I get product feeds from about 5 vendors a ...
3
votes
1answer
193 views

Could 'Maximum Server Memory' be too low for large batched delete with captured output?

I'm trying to improve the performance of a SQL server database backup program that will create a new database to hold one years worth of report records, delete report records out of the main database, ...
3
votes
1answer
422 views

Postgres 9.3 - deletes terribly slow for 10 minutes after dropping indexes/constraints

If I run a script which drops indexes (17 of them, CONCURRENTLY makes no difference except that without it I get the occasional deadlock), then constraints (20 constraints on 18 tables) and then run a ...
2
votes
2answers
111 views

Deleting rows from a table with a very small amount of expansion space

I have multiple tables that I have backed up the data in, and now am deleting the old data for the time, archiving is not an option due to space constraints at this time. I have tried running the ...
2
votes
3answers
8k views

Clear all tables with one DELETE

Is it possible to clear all tables with one request? I have tried the following with no result. DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as ...
2
votes
2answers
77 views

Delete all highscores from a certain country

I have two tables: tbl_players tbl_highscores I need to delete all highscores that are registered from a certain country. My problem is that the language are set per player, rather than by ...
2
votes
2answers
520 views

How do I Delete A Database And Log Files With SQL 2008 Script?

While working on making an automated Delete and Restore script for my backups I ran into a snag. I can not delete the database; however, the delete button works exactly how I need it to. Can anyone ...
2
votes
2answers
543 views

Inserting data to a table which is being cleaned up by SQL job at the same time

Our app uses a table in a database and we have a Sql job set up to run overnight which executes a stored procedure which just deletes data older that from the table. DELETE FROM MyTable ...
2
votes
1answer
1k views

Delete rows in a table that has multiple FKs

I need to delete all the rows in the ContentData table that have the word 'DV95' in the htmldata column. Here is what I am using: BEGIN TRANSACTION DELETE Contentdata FROM Contentlinks INNER JOIN ...
2
votes
1answer
448 views

Mysql foreign key cascade delete

Does mysql have a way to delete all sub links if a row was deleted from a "parent" table? Example table setup is below, the issue I am trying to avoid is having to maintain a script that deletes the ...
2
votes
1answer
5k views

MySQL Query: Delete from 'table2' where column = 'value' IF column in 'table1' = 'value'

I am trying to execute a MySQL query to delete rows from 'table2' where column = 'value' IF column in 'table1' = 'value' I have 2 tables... Table 1 is called accounts Table 2 is called ...
2
votes
2answers
5k views

MySQL - automatically update all references to specific table to use on delete cascade?

I have a specific table for one of my software packages on a MySQL 5.1 box which I need to drop a few hundred entries. These are user names with names in uppercase, the application is case specific ...
2
votes
1answer
539 views

Deleting 200M+ rows from InnoDB table

Basically, we have a table with the following structure: CREATE TABLE `reqs` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `reqid` BIGINT(20) NOT NULL, `from` BIGINT(20) NOT NULL, `to` ...
2
votes
4answers
850 views

Delete the N oldest entries grouped by multiple columns

I´d like to delete the oldest entries of my database, but in each category shall remain at least N words of each char. Structure of TABLE words id | word | char | category For example I have the ...
2
votes
1answer
548 views

Will deleting data in master automatically delete data in slave?

I have data replication setup on MySQL. If I delete data on the master, will replication know data was deleted and automatically delete the same data on slave?
2
votes
1answer
839 views

Postgresql revert back database value

I have postgresql database. My table name is tblvoippolicy. Now, problem is by mistake i have deleted some records from table. When I get the data file using following commands. select * from ...
2
votes
1answer
67 views

Deleting large amounts of cascading records with minimal locking

I'm looking for a strategy to purge large amounts of logically deleted data from a MySQL database. The deleted records are in a relatively small table, but are referred to with many ON DELETE CASACDE ...
2
votes
1answer
85 views

How is it possible that two deletes give a deadlock, if they do have different primary key data

I am getting a deadlock by the same delete stored procedures that is called by two different threads for deleting two concurrent records. The deadlock happened on the primary key index of the X ...
2
votes
1answer
2k views

DELETE from Linked Server table using OPENQUERY and dynamic criteria

I'm attempting to run a DELETE statement from my local SQL Server to delete appropriate records from a table in my remote linked server using OPENQUERY(). I'm using the following code, but it seems ...
2
votes
1answer
724 views

Error deleting more than 1 row

I have a stored procedure that is supposed to delete several rows from a table. It does not work and gives me the error: The transaction ended in the trigger. The batch has been aborted I tried ...
2
votes
1answer
1k views

Inherit audit columns and triggers

Background I have a database with dozens of tables, some of which should have audit columns. Problem I'd like to avoid the drudgery of creating audit columns manually, and write the update and ...
2
votes
1answer
225 views

clustered and covering index ignored on delete statement. Table scan occurs

Why would SQL Server 2005 find it more efficient to perform a table scan instead of using the available clustered index on the primary key (and only the primary key)? DISCLAIMER: There is also a ...
2
votes
2answers
6k views

remove duplicate rows in mysql table that does not contain primary key

I have a table item having just one column name:- name ----- toys shirt mobile Shirt speaker Toys .... .... I am trying to delete duplicate rows except one duplicated record with the help of below ...
2
votes
2answers
161 views

How can I delete repeated rows?

I have a system that gathers RSS from many sites including UTF-8 based sites (non-english sites). I have tried to avoid repeated rows but I have repeats in my table. RSS_items (id(auto_increment) ...
2
votes
0answers
68 views

speed up ghost cleanup

In SQL Server I have a table that is filled often and also gets deleted often. Because several threads write and erase data from that table I cannot even truncate it because there is always someone ...
2
votes
1answer
105 views

What is the equivalent of a “Certificate of Destruction” when using a cloud-hosted database?

We have a Rails application hosted by Heroku and using an Heroku Postgres database. Some of the information we store is considered to be sensitive by our users, and we've been asked if we can provide ...
2
votes
4answers
729 views

MySQL: Keep the 10 most recent rows for each user

I have a table saving scores for a game that have 3 columns (more in practice, but it is easier for everyone this way): userid : the id of the user that got the score timestamp: the time the score ...
1
vote
3answers
909 views

Deleting data from a large table with replication

I need to delete all the rows from a given table. The table contains millions or records. The master database is being replicated to several slaves and I wish to do that without creating a replication ...
1
vote
2answers
2k views

How to delete rows from a table when we're joining and return data?

I have two queries: The first one is as below: SELECT * FROM batch bp INNER JOIN sender_log sl ON sl.id=bp.log_id AND bp.protocol='someprotocol' The second query is used to delete rows: ...
1
vote
3answers
7k views

Do I need to reindex mysql table after bulk delete?

I have a table in MySQL with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
1
vote
1answer
154 views

Soft delete to improve performance: all wrong?

A few years back I read soft delete would improve performance of deletion, because instead of actually removing the item and impact the clustered (and all other) indices, it would just change a value. ...
1
vote
1answer
191 views

Full table scan - high water mark

When doing a full-table scan, why does Oracle scan all the rows beneath the high-water mark? Doesn't it know that some of the rows have been deleted and that they shouldn't be scanned?
1
vote
1answer
203 views

Unable to delete a record in Mysql

Let's say I have table name Company with two columns cname, location. In the table I am having two records with cname |location Asmetric |Dallas Sophore |Washington on this table when I ...
1
vote
1answer
36 views

Delete query does not appear to open space on the SQL Server

Our SQL Server (version 2012) is housed on a cloud server, for which we pay whenever we need additional space. We determined that we could delete some of the data to prevent the need for continually ...
1
vote
2answers
302 views

SQL Server : trigger FOR DELETE

I want to create a trigger, which does not delete a customer if his name is Peter. How can I do that? CREATE TRIGGER trgCUSTOMER ON customer FOR DELETE AS BEGIN IF (SELECT name FROM deleted) not ...
1
vote
1answer
114 views

Delete from table where two of the three keys in another select

I have tableA which has 4 columns: IDA (bigint), IDB (int), IDC (varchar(255)) and comment (varchar(255)). The primary key is (IDA, IDB, IDC). I'm required to delete the records where the count of ...
1
vote
2answers
1k views

How to avoid locks by large transactions?

We have a large application running, that requires cleanup for performance reasons. This was however not forseen when designing the application. Basically, the delete is executed from a stored ...
1
vote
2answers
957 views

Approaches for deleting unnecessary records from tables

We have a database which stores the temperatures of 20 thermometer every 7 seconds. We want to delete all records in the way that every minute holds just one tempereature instead of 8 which are older ...
1
vote
1answer
50 views

Query optimization in case of TIMESTAMPDIFF

I have a table where I need to delete all rows older than 5 hours. Currently I'm doing: delete from my_table where TIMESTAMPDIFF(HOUR, from_unixtime(round(created_on/1000)), now()) > 5 The ...
1
vote
2answers
344 views

What is the best way to purge data from a table with minimum damage to performance?

When you purge records from a database table, deleting them, you have impact on the logical and physical structure of the target table, as well as indexes and statistics. What maintenance should be ...
1
vote
3answers
110 views

SQL Delete rows where a dataname will equal mulitple variables

Researching similar common queries have really helped but I am still none the wiser as to my issue. To note that the database I intend to use this on contains around 1000 rows (same 2 columns listed ...
1
vote
1answer
977 views

MySQL: update instead of delete if foreign key constraint?

I have a bit of a strange question. I know about insert on duplicate key update. My question is, is there something similar for deletes that fail because of foreign key constraints? For example: ...
1
vote
2answers
3k views

Freeing space after deleting rows [duplicate]

I have a SQL Server 2008 instance and one table is quite large (log data) so I delete old logs. Now I start to read articles on this problem, here is one. As I see it there is no solution! If I ...
1
vote
1answer
11k views

Delete from table where multiple fields match select subquery from other table

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table. This is what I have so far, though it doesn't work: ...
1
vote
1answer
949 views

Delete entry from related tables

I can not use InnoDB engine because of some dependency troubles in LuaSQL and PtokaX. My current query is something like: DELETE e.*, m.* FROM entries e INNER JOIN magnets m ON m.eid = ...