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

learn more… | top users | synonyms

0
votes
0answers
2 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
24 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
40 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 ...
3
votes
1answer
89 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
2answers
79 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
249 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
37 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
30 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
53 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 ...
0
votes
1answer
56 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
42 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 ...
1
vote
1answer
82 views

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

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, ...
0
votes
0answers
12 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
40 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
46 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
30 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
51 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
31 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 ...
2
votes
1answer
65 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 ...
1
vote
1answer
67 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
26 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 ...
0
votes
2answers
47 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 | ...
0
votes
1answer
21 views

Run SQL requests generated by some script

I'm using this script to delete data from all the tables in my database. It generates DELETE statements in proper order, each request in one row : DECLARE @schema_name sysname; DECLARE @table_name ...
20
votes
6answers
3k views

DELETE command not completing on 30,000,000 row table

I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data made by an error by our programmer. Before I add any new, ...
0
votes
0answers
84 views

delete millions of rows from multiple tables

Hi we have to delete between 40 and 55% of records from several tables that have between 40 million and 100 million rows. Is it better to copy the rows we want to keep to a new table, drop the source ...
1
vote
1answer
437 views

Remove all records with duplicates in db2. (Not just the duplicate records)

How can I remove all the records with duplicates in db2. I have looked at various answers but they only remove the duplicates leaving one record from that set in the table. This is what I found ...
0
votes
1answer
339 views

DELETE from Linked Server table using OPENQUERY and dynamic criteria

I'm attempting to run a DELETE statement from my local MSSQL Server to delete appropriate records from a table in my remote linked server using OPENQUERY(). I'm using the following code, but it seems ...
1
vote
1answer
93 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?
0
votes
1answer
60 views

decrement all columns entry when row is deleted

I have a column in a table containing the row_number, some kind of id number to identify the row. When I delete one row, I need the other rows to have their row_number decremented, to keep them ...
6
votes
2answers
241 views

Why doesn't a delete increase unused space in a table?

If I populate the following table: IF OBJECT_ID ('dbo.CleanTableTest', 'U') IS NOT NULL DROP TABLE dbo.CleanTableTest; GO CREATE TABLE dbo.CleanTableTest (FileName nvarchar(4000), ...
0
votes
1answer
71 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 ...
1
vote
2answers
128 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 ...
2
votes
2answers
88 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
312 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 ...
1
vote
3answers
2k 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 ...
0
votes
0answers
38 views

Update on table deletes the record

I'm having a problem in my oracle database, I have a table PAYMENTS which has an ID (PK) and when I want to update a record: UPDATE PAYMENTS SET AMOUNT = 2000 WHERE ID = '1'; The record with ID = ...
1
vote
1answer
75 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
3answers
313 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 ...
0
votes
0answers
124 views

insert record to child table which doesn't map to parent table - possible?

Actually I've 2 relevant questions: insert record to child table which doesn't map to parent table - possible? ON DELETE NO ACTION - when a record in parent table is deleted, it should affect ...
0
votes
2answers
138 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 ...
0
votes
1answer
64 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 ...
1
vote
1answer
64 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
136 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 ...
0
votes
0answers
14 views

Lower resolution of record on MySQL

The database I'm currently working on logs about 50 columns of data per second on our plant. The current high frequency logging is due to ongoing test. However, going long term, this would not be ...
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 ...
0
votes
0answers
44 views

Update existing (old) rows vs delete and insert when needed

I'm building a database system that needs to store login tokens for a system that validates if a users login token is valid or outdated. The table is pretty simple UserID int Primary Key LoginToken ...
0
votes
3answers
194 views

Delete all the data from all tables

I have a little problem. I have to delete all the data from our database. I don't want to drop tables, only delete the data sets. These are 47 tables in total. I tried the following: Delete ...
0
votes
1answer
36 views

Can having a LIMIT on DELETE degrade performance dramatically?

I have a database project in php and mysql. For, hopefully redundant, data security I added a LIMIT 1 to the delete($uniqueID) php function we use. Nobody cared because records really aren't ...
3
votes
1answer
408 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 ...
0
votes
3answers
162 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 ( ...