In the database structured query language (SQL), the DELETE statement removes one or more records from a table.
0
votes
2answers
31 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 ...
0
votes
1answer
32 views
Need ideas about OPTIMIZE TABLE
Looking at a database with 10 tables and fairly active at by-the-hour changes. On the first of each month, I purge some rows from 3 tables to remove outdated material and keep the size down. All of ...
1
vote
1answer
62 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 ...
2
votes
4answers
131 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 ...
1
vote
1answer
48 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?
0
votes
2answers
86 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
152 views
Very slow DELETE in PostgreSQL, workaround?
I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
3
votes
1answer
116 views
Why is truncate DDL?
I have an interview question, which being asked during my interview. I answered the question, but interviewer was not convinced with my answer. So, please let me know the what would be the correct ...
2
votes
0answers
37 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 ...
1
vote
1answer
155 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
54 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 ...
2
votes
1answer
183 views
Most efficient way of bulk deleting rows from postgres
I'm wondering what the most efficient way would be to delete large numbers of rows from PostgreSQL, this process would be part of a recurring task every day to bulk import data (a delta of insertions ...
2
votes
2answers
93 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
...
1
vote
1answer
116 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 ...
2
votes
1answer
157 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 ...
3
votes
3answers
258 views
How to delete large amount of data from a sql table?
We ran out of disk space and we have a table with about 10million records.
We'd like to delete the old records which are about 2million.
What are the impact of just deleting these using a simple ...
4
votes
2answers
166 views
Delete all rows within transaction: what would happen to other transactions?
I currently have a function in PostgreSQL that creates a temp table, fills it, performs some tricky selects and returns results. postgresql's temp tables are local to functions, so this helps me avoid ...
4
votes
1answer
172 views
Performance tuning a cascading delete with many foreign keys
I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data ...
1
vote
1answer
196 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
125 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 ...
3
votes
2answers
144 views
Can I do anything about a very long running KILL command?
I've got a table with 103M+ records in it and takes up 70GB on the disk. I decided to DELETE (in one statement) 70% of the records.
As expected, this took an awfully long time to run. I decided to ...
13
votes
2answers
651 views
Why does DELETE leave a lingering effect on performance?
At the end is a test script for comparing the performance between a @table variable and a #temp table. I think I've set it up correctly - the performance timings are taken outside of the ...
0
votes
1answer
74 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 ...
2
votes
2answers
395 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?
4
votes
2answers
101 views
DELETE LOW_PRIORITY row visibility
I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and ...
4
votes
1answer
1k views
Why is DELETE so much slower than SELECT, then DELETE by id?
I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete ...
1
vote
2answers
1k 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 ...
3
votes
3answers
643 views
Optimizing DELETE Query on MySQL MEMORY Table
I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. ...
1
vote
2answers
79 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
2answers
1k views
MySQL: Delete all but last N records
Consider the following table:
mysql> DESCRIBE pixels;
+---------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default ...
2
votes
2answers
106 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) ...
1
vote
1answer
288 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
1k 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
797 views
I need to run VACUUM FULL with no available disk space
I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
3
votes
2answers
3k views
What to use, CASCADE DELETE rule or ON DELETE trigger for one to many constraint?
I would like to ask about how to handle this relationship. For better view I've tried to simplify and convert my situation into how I think StackOverflow question voting has (sorry if not :-)
I ...
2
votes
2answers
67 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 ...
3
votes
1answer
295 views
Oracle explain for cascading delete is wrong
My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it.
If I hit the explain button on a statement like this ...
5
votes
2answers
674 views
Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios
I need to keep track of deleted items for client synchronization needs.
In general, is it better to add a tombstone table and a trigger that tracks when a row was deleted from the server database - ...
8
votes
1answer
252 views
Disk file effects of delete and vacuum
I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
-4
votes
5answers
2k views
Safely deleting a SQL Server log file (.LDF)
I can make sure my app is in a consistent state.
I can rollback all the uncompleted transactions if any (just in case) it's ok
I can DETACH the database
What do I need the log file for after that?
...
4
votes
2answers
394 views
How to implement soft deletes?
What is your method of implementing soft deletes in a database?
Ideally the solution would allow for
decent performance on large tables
take relationships into account
take unique keys into account
...
2
votes
1answer
231 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 ...
1
vote
2answers
277 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
269 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 ...
2
votes
1answer
304 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 ...
6
votes
3answers
1k views
What is the best approach to run a DELETE query on 92K of 4 million rows?
Options that have occurred to me:
One big delete statement. For example:
delete from x where (y = 'x' and x = 'y') or (y = 'a' and x = 'b')
Same as above, but chunk in smaller pieces, 10k at a ...
12
votes
3answers
812 views
Slow deletion of records when a trigger is enabled
Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve.
http://support.microsoft.com/kb/2606883
Ok so I have an ...
7
votes
3answers
602 views
Deleting all duplicates
I am trying to delete all the duplicates but keeping single record only (shorter id).
Following query deletes duplicates but take lot of iterations to delete all copies and keeping original ones.
...
4
votes
2answers
4k views
can I resolve a deadlock with the rowlock hint?
I have a large delete stored proc and I've reproduced a deadlock in a situation where the deletes would not have deleted anything.
It looks like the part of the stored proc that hit a deadlock was ...
4
votes
2answers
3k views
Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)
Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn.
First, I do this:
select max(ora_rowscn) from ...
