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
56 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 ...
1
vote
0answers
7 views

Is it possible to cascade delete a column value? [duplicate]

So say I delete catID from table A and table B has the same foreign key value? It's hard to explain so I'll try and show you with the two images below: items table Is it possible to delete the ...
1
vote
1answer
398 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
827 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 ...
4
votes
2answers
344 views

Which command Deletes and Frees Space

I'm reading through the 98-364 exam book and there's a question I can't quite answer: "What command would you use to remove all records from the Contact table that do not have a country of USA ...
3
votes
2answers
6k views

MySQL: Delete all but last N records

Consider the following table: mysql> DESCRIBE pixels; +---------------+-------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default ...
21
votes
3answers
39k views

How to find out who deleted some data SQL Server

My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters). I thought this could be ...
0
votes
1answer
320 views

Delete files from disk, with a job, respecting some rules ( SQL Server 2008 ) [closed]

We have An application that our customers use to download and upload files. I need to delete these files from disk after 30 days, of if it was downloded, in 7 days. How can i Use the script below, ...
1
vote
1answer
71 views

Delete rows where matched value is twice as much

I'm trying to match two tables with two columns, table1 and table2. Each has id and price columns. I want the query to match each id and compare the price, then delete the row if the price in the ...
3
votes
1answer
340 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 ...
0
votes
3answers
54 views

Use “NOT IN” in the same table, Mysql

I have a table like that: table1 +----+------+--------------+ | id | name | phone_number | +----+------+--------------+ | 1 | A | 111111111111 | +----+------+--------------+ ...
1
vote
2answers
205 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
2answers
205 views

PostgreSQL : dependencies on a schema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
1
vote
3answers
34 views

To trigger or not to trigger- That is the question

I have a mysql table that tracks page views. Each row includes a date stamp. I want to remove all rows that are over 30 days old on a regular basis. Could, or should, I trigger an action on each ...
7
votes
2answers
3k views

Why is truncate DDL?

I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ? ...
-1
votes
2answers
63 views

how can I delete records when my disk drive is full?

My data folder of SQL as filled up the space of my local drive. I have 150KB free space left in the drive. So I have to delete some records to regain space. But when I run the delete query it says ...
1
vote
2answers
68 views

Should I stop an awfully inefficient MySQL DELETE statement?

I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode: DELETE table FROM ( SELECT MAX(id) id, field FROM table GROUP BY field ) temp_table INNER ...
3
votes
1answer
106 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, ...
1
vote
2answers
87 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 ...
6
votes
2answers
139 views

MySQL: delete…where..in( ) vs delete..from..join, and locked tables on delete with subselect

Disclaimer: please excuse my lack of knowledge about database internals. Here it goes: We run an application (not written by us) which has a big performance problem in a periodic cleanup job in the ...
0
votes
2answers
56 views

After Exporting Database should be empty

I am exporting data from database for that I have written following query: SELECT ParameterID, Name,Type,Datatype,LogDate FROM Param_Ems WHERE DataType = 10 INTO OUTFILE 'C:/tmp/ParamData.csv' FIELDS ...
0
votes
0answers
37 views

How to retrive deletes Mysql table data back?

I accidently delete data from mysql but i want it back. I am fedora operating system is use for installing Mysql. Please help ASAP because data is very critical
1
vote
1answer
50 views

To find data in Multiple tables and delete it?

I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the ...
0
votes
0answers
20 views

Synchronization of DB

I have 2 databases and I need to synchronize data between them. I need to delete all the data from DB1 and then insert data to DB1 from DB2. What solution will you suggest drop all the foreign ...
1
vote
0answers
92 views

MySQL - Preventing lock wait timeout when deleting rows from self

I have a table of around 500k rows (not big data), that I need to query against itself and then delete some of the rows which don't match the filters. At the moment my query looks like this: DELETE ...
0
votes
1answer
23 views

How to subtract records from a table based on their existence in another

I have an Access 2010 table Assignment key Empl Number key Year Number key Week Number key Proj Number It assigns employees to zero or more projects for given work weeks, thus, all ...
0
votes
1answer
82 views

How to remove data from multiple tables with different foreign keys without using ON DELETE CASCADE?

I have the following SELECT statement: SELECT * FROM "Users" u INNER JOIN "Calendars" c ON (u."UserId" = c."UserId") INNER JOIN "Actions" a ON (c."CalendarId" = a."Calendar_CalendarId") WHERE ...
1
vote
0answers
56 views

MySQL bulk deletions

I am currently looking at how to most efficiently delete rows from a table in bulk. The database I'm using is MySQL with innodb. The table I want to delete from can be any size, but will usually be ...
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 ...
3
votes
1answer
107 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 ...
4
votes
1answer
7k 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 ...
2
votes
1answer
4k 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 ...
3
votes
2answers
105 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
1answer
287 views

Delete using a composite key

I am trying to wrap my head around creating a delete statement for a table with a composite key. I would like to create something like: DELETE FROM table_example1 WHERE COLUMN1, COLUMN2 ...
0
votes
1answer
62 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. ...
0
votes
0answers
34 views

I have a table from which I can retrieve information but I cannot update or delete information

I can update and delete items via PhpMyAdmin. I changed the owner to mysql. My program is a clone of insert, update, and delete that is functioning for other tables within the database. I receive a ...
0
votes
2answers
100 views

How many records should be deleted when batch delete is used?

I am going to migrate a large amount of data from one table to other table. The target data source table has foreign key constrains, so I am not able to use DELETE with OUTPUT clause, but need to have ...
2
votes
1answer
77 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 ...
0
votes
1answer
105 views

SQL Server Deadlock on delete

I have a query like this: with xx as ( SELECT [FilePath], ROW_NUMBER() over (partition by [FilePath] order by [FilePath]) as RowNumber FROM [dbo].[foo] ...
2
votes
0answers
53 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 ...
0
votes
0answers
14 views

Releasing space after deleting datafiles

I'm deleting old backup tablespaces with option using contents and datafiles in TOAD. Datafiles were deleted, but no space was released: Filesystem GB blocks Free %Used Iused %Iused ...
0
votes
0answers
201 views

Access 2010 delete and append queries run partially then give “Read Only” error

I have a number of append and delete queries written in Access 2010 that link to and modify tables on our SharePoint 2010 site. These queries have run without problem for months, but suddenly they've ...
0
votes
0answers
52 views

Purging Data that has Foreign Keys

I have a transaction database that we recently went live with. The number of rows in the tables are starting to climb into the millions. So I am looking at how to purge the older data. I have written ...
0
votes
1answer
69 views

DELETE statement execution cancellation in SSMS

I ran a delete statement with wrong WHERE condition (which deletes all records from table instead of just 10 records) but during execution I found it and CANCELLED the execution in sql server ...
1
vote
1answer
96 views

Delete Duplicates Leaving One of Two Identical Records [closed]

Delete Duplicates Leaving One of Two Identical Records One of my MySQL database tables contains four BLObs in each row. When it was first set up the table had no keys. However when I now try to add a ...
1
vote
0answers
59 views

MySQL 5.1.73-cll and engine MyISAM PRO AND CONS FOR THE FOLLOWING 2 'DELETE' APPROACHES [closed]

For MySQL 5.1.73-cll and engine MyISAM what are the pros and cons for the following two possibilities of 'deleting' rows(let's say that a user want's to delete his profile/delete some of his products ...
1
vote
1answer
89 views

MySQL Deadlock - accessing different primary key values also creating deadlock

I went through MySQL logs and found the reason for deadlock 1st thread is trying to execute below query. Which is waiting for thread 2. UPDATE M_SAMP SET MM_Q_IND=0 WHERE M_ID IN (SELECT M_ID FROM ...
0
votes
0answers
36 views

How to find Deleted and Updated record tables in MYSQL database?

I have scenario like below. I need to find Deleted records and updated records effected tables in mysql. Ex: I update my Name or Email Id in UI that is effected in some tables either one table or ...
1
vote
2answers
2k 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` ...
0
votes
2answers
51 views

Delete the second record where a user has two records

I have a simple database scheme, which due to previously being badly set up has numerous duplicate records. record_table |--------------------------------------------------------| | id | subid | ...