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

learn more… | top users | synonyms

2
votes
1answer
526 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
59 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, ...
2
votes
1answer
496 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
721 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
198 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
4k 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
151 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
3answers
76 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 ...
2
votes
0answers
140 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
1answer
80 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
351 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
95 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
4k views

MySQL: Delete all but last N records

Consider the following table: mysql> DESCRIBE pixels; +---------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default ...
1
vote
2answers
332 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
1answer
44 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
2answers
5k views

Delete an oracle instance, the right way

I want to delete an oracle instance (oracle 10.2.0.4.0) created in the AIX 6.0 OS. I know i can delete all the dbf and ctl files in the Terminal but i thinks that's no the best way to do it. I think ...
1
vote
1answer
60 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
473 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
3answers
88 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
678 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
1answer
591 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
2k 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
422 views

Large table delete in mysql

I want to delete a table with 10,000,000 records. Due to some restrictions I can't do truncate on that table (I don't have the DROP privilege), I can think of two options: DELETE QUICK To set some ...
1
vote
1answer
317 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?
1
vote
2answers
1k views

Fastest way to delete matching rows in MySQL?

I have more than 10 million records in a table. I want to delete the records where the email column contains .ac.. What is the fastest method to perform the task? Table Schema CREATE TABLE `master` ...
1
vote
1answer
123 views

Mysql setting a record as deleted or archive

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is ...
1
vote
1answer
685 views

Delete by primary key takes too long

What would make a query that deletes a row using the primary key of the table .. take too long ? This is a table with about 1.4M rows that gets .. 70%/30% read/write . I don't know where to look. ...
1
vote
2answers
3k 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 ...
1
vote
2answers
639 views

Deny insert/delete for all users in SQL Server 2005

In our database we have about 20 users, that is used be some program (and users are added with this program). Now there was new requirement to use 2 database and do not allow delete data from one ...
1
vote
1answer
527 views

MySQL master master replication, safe to delete rows on both masters?

We have a MySQL (v5.1.61) cluster with two masters (and two slaves). We have a table logs with a primary key column logID. This log table grows rather huge, and we'd like to delete data from it. So ...
1
vote
1answer
946 views

mysqldump: replace drop table in output with delete?

I am using mysqldump to dump some records in a table: mysqldump mydatabase mytable --where="field=value" this emits script that includes drop table mytable statement. Since I use the --where ...
1
vote
1answer
340 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 = ...
1
vote
1answer
469 views

mysql query - how to delete duplicates based on highest int?

I'm running a reverse auction website and I need to delete the duplicate bids and keep only the highest amount . lastPriceBid_rounded is the "amount". The duplicate is defined by the userId. ...
1
vote
1answer
250 views

Delete word, its meanings, its meaning's example sentences from DB

I have three tables as below (simplified for demonstration): words ===== integer id text word meanings ======== integer id integer word_id text meaning examples ======== integer id integer ...
1
vote
1answer
51 views

DELETE seems to hang

The following command: DELETE FROM hours WHERE id IN (SELECT id FROM holdkey WHERE persons_id=60) seems to hang indefinitely. However, the corresponding SELECT * command completes in a fraction of ...
1
vote
1answer
50 views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
1
vote
0answers
42 views

Low batch Batchrequests/sec

I am running this query to do massive deletion in my database. I am looking at Batchrequests/sec and it is quite low (like 15% of what my server can handle) Is there any way to rewrite this query to ...
1
vote
0answers
75 views

Why is this query stuck if there are no waits?

I am working with a vendor's activity logging system. One of the tables has 70+ million records and is constantly increasing at several hundred records per minute. They have a utility to delete old ...
1
vote
2answers
154 views

How do automaticly delete rows from other tables

I have a MySql DB. I have a table - recipes. Also have 2 tables -ingredients and instructions. both have for each entry - a foreign key to the related recipe. I want that when I delete an entry from ...
1
vote
1answer
462 views

Optimization: Delete with Top and minimize the where clause date range

I have a delete statement that get run repetitive until all the data are removed. Delete TOP (1000) From Log Where CreationDate < '2011-Dec-31' AND pKey NOT IN (...) I am considering of ...
0
votes
2answers
2k views

Delete rows in parent table based on criteria in child table - Oracle

I have a table B with a foreign key to table A. I want to DELETE some rows in table B, and I also want to DELETE their parent rows in table A. However, the delete criteria is based on table B. The ...
0
votes
3answers
440 views

MySQL Large DELETE with JOINs

I have a large table players with ~20MM rows which joins to a table stats with ~300MM rows, among others (photos, ...). I want to DELETE all records for players that were born_on before 1950. I ...
0
votes
3answers
98 views

Slow DELETE in MySQL with a subquery

I am running the following DELETE SQL on a MySQL database on a MyISAM/utf8 table. The SQL is called in a loop, once time per Users (in this case):- DELETE FROM regulatory WHERE documentid IN ( ...
0
votes
1answer
367 views

Delete Query takes a long time

I have a innodb table that counts circa 1 billion entries. I started a delete operation (with a small where condition on an indexed column) that should delete a lot entries, leaving circa 100 million. ...
0
votes
1answer
149 views

Effective duplication of DELETE … LIMIT in Pg

I have an unusual problem: I need to delete a certain number records from a table, of a particular type; in certain non-compliant DBs it would be easy to accomplish this with DELETE FROM sometable ...
0
votes
1answer
105 views

Multiple source CASCADE DELETE behavior

I'm having trouble understanding Postgres's CASCADE DELETE behavior. If table_A REFERENCES table_B, and the column referenced could have multiple instances of the same value, will the table_A row be ...
0
votes
1answer
121 views

myISAM data loss at DELETE / INSERT

I have a linked table id_group | id_user. Every time I add a one or more users to a group I DELETE all the links and INSERT the users again including the new one(s). This is how the framework of the ...
0
votes
1answer
15 views

DELETE from OPENQUERY alias not working

I'm trying to use the following code to execute a DELETE statement on a linked server. I'm trying to delete all records from the remote server where there is a match on a certain ID field in both the ...
0
votes
1answer
50 views

SELECT count vs DELETE JOIN. Why/how are they different?

Can someone explain in what way these queries could be different and the results are not the same? > begin; Query OK, 0 rows affected (0.01 sec) >select count(*) from tableA a JOIN tableB ...
0
votes
1answer
31 views

ON DELETE CASCADE pseudo delete

For data integrity ON DELETE CASCADE is necessary but it's really dangerous when a delete affects like a Chain Reaction! I know one way to prevent this is to make a pseudo delete by putting a ...