In the database structured query language (SQL), the DELETE statement removes one or more records from a table.
4
votes
4answers
41 views
Fast way of delete MySQL record
I have a 1.2 million record in a database table( it use index column also)
I want to delete old 1 million record. I try to do it using PhpMyadmin but it take more time.
DELETE FROM `table` WHERE ...
0
votes
0answers
9 views
IIS difference: Remove and Deploy -> Delete Site and Content [migrated]
One can remove a site from IIS by the right mouse menu.
However this gives you two options:
"Remove"
"Deploy -> Delete Web Site and Content"
What is the difference?
0
votes
1answer
19 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
2answers
57 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
40 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
101 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
133 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
60 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
121 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
226 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
153 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
40 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
190 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
56 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
260 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
98 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
127 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
168 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
273 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
205 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
205 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
132 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
176 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
702 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
75 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
413 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
106 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
2k 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
698 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
80 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
113 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
297 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
878 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
307 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
714 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
262 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
431 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
246 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
290 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
281 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
313 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
865 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 ...
