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

learn more… | top users | synonyms

1
vote
2answers
775 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
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 ...
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
2answers
219 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 ...
1
vote
1answer
2k views

MySQL master master replication, safe to delete rows on both masters?

We have a MySQL (v5.1.61) cluster with two masters (and two slaves). We have a table logs with a primary key column logID. This log table grows rather huge, and we'd like to delete data from it. So ...
1
vote
1answer
519 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
277 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 ...
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
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 ...
1
vote
2answers
67 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 ...
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 ...
1
vote
0answers
90 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 ...
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
0answers
58 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
196 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 ...
1
vote
0answers
44 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 ...
1
vote
0answers
86 views

Why is this query stuck if there are no waits?

I am working with a vendor's activity logging system. One of the tables has 70+ million records and is constantly increasing at several hundred records per minute. They have a utility to delete old ...
1
vote
2answers
180 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
1answer
538 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 ...
0
votes
1answer
59 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
2answers
3k views

Delete rows in parent table based on criteria in child table - Oracle

I have a table B with a foreign key to table A. I want to DELETE some rows in table B, and I also want to DELETE their parent rows in table A. However, the delete criteria is based on table B. The ...
0
votes
3answers
692 views

MySQL Large DELETE with JOINs

I have a large table players with ~20MM rows which joins to a table stats with ~300MM rows, among others (photos, ...). I want to DELETE all records for players that were born_on before 1950. I ...
0
votes
3answers
51 views

Use “NOT IN” in the same table, Mysql

I have a table like that: table1 +----+------+--------------+ | id | name | phone_number | +----+------+--------------+ | 1 | A | 111111111111 | +----+------+--------------+ ...
0
votes
3answers
194 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 ( ...
0
votes
1answer
976 views

Delete Query takes a long time

I have a innodb table that counts circa 1 billion entries. I started a delete operation (with a small where condition on an indexed column) that should delete a lot entries, leaving circa 100 million. ...
0
votes
2answers
99 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
28 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 ...
0
votes
1answer
76 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 ...
0
votes
1answer
661 views

Effective duplication of DELETE … LIMIT in Pg

I have an unusual problem: I need to delete a certain number records from a table, of a particular type; in certain non-compliant DBs it would be easy to accomplish this with DELETE FROM sometable ...
0
votes
1answer
148 views

Multiple source CASCADE DELETE behavior

I'm having trouble understanding Postgres's CASCADE DELETE behavior. If table_A REFERENCES table_B, and the column referenced could have multiple instances of the same value, will the table_A row be ...
0
votes
1answer
401 views

I have delete database from mysql but storage is not freed

I have deleted multiple databases (schema) from mysql on amazon RDS but the storage is not freed, is there anything else I should do to free up the storage? I have a mysql database that the total ...
0
votes
1answer
134 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 ...
0
votes
1answer
77 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 ...
0
votes
1answer
309 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, ...
0
votes
1answer
83 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 ...
0
votes
1answer
161 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 ...
0
votes
1answer
47 views

ON DELETE CASCADE pseudo delete

For data integrity ON DELETE CASCADE is necessary but it's really dangerous when a delete affects like a Chain Reaction! I know one way to prevent this is to make a pseudo delete by putting a ...
0
votes
1answer
36 views

Is it possible (or good practice) to register data upon Deletion of a record? What would be a better solution?

The problem is the following. A company has one or more representatives (a person who speaks on behalf of the company). When a representative is deleted, it's desired to keep it in an list called ...
0
votes
2answers
1k views

Massive MySQL delete from CSV file

To insert massive CSV file into MySQL, there is LOAD DATA INFILE, but is there any similar command to delete massive entries with keys stored in a massive CSV file? If not, what would you see as the ...
0
votes
1answer
1k views

Deleting Record from Linked Table Slow in MS Access

I have a Microsoft Access database that is linked to a SQL Server 2000 backend. It has one form with several subforms, all using information from linked tables. When I try to delete a record from one ...
0
votes
2answers
633 views

Trigger to delete inserted record if it doesn't exist in another table

I have a question. I have two tables: zip_code (zip_id, zip_code, state) - this is a static table that stores areas I service tech_zips (tz_id, tech_id, zip) - this is where I store what zip codes ...
0
votes
1answer
286 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 ...
0
votes
0answers
36 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
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
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 ...
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
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
1answer
104 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] ...
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
196 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 ...