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
24 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
50 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 ...
3
votes
1answer
62 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, ...
0
votes
0answers
34 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
0
votes
2answers
55 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 ...
1
vote
1answer
46 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 ...
5
votes
2answers
103 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
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
54 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
19 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
39 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
50 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
100 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
90 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
266 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
42 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
33 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
69 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
79 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
47 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
155 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
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
116 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
50 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
42 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
77 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
39 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
74 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
77 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
31 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
49 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
26 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
4k 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
98 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
641 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
500 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
103 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
69 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
257 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
104 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
161 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
92 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
430 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
3k 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
40 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
80 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
403 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
165 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
161 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
69 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 ...