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

learn more… | top users | synonyms (1)

1
vote
1answer
23 views

Can I use table aliases in a MySQL DELETE statement?

This does not work: DELETE FROM topics AS t , posts AS p USING t INNER JOIN p WHERE t.id=p.topic_id AND t.id = '5599'; whereas this does: DELETE FROM topics, posts USING topics INNER JOIN posts ...
1
vote
1answer
62 views

How to delete duplicate rows in merge replication

Somehow two rows in a table that is party to merge replication are identical, including their rowguid. I have no idea how this happened, it is only with two rows in my entire database (which is used ...
1
vote
1answer
23 views

Oracle SQL script - passing a parameter

I have an Oracle SQL script like this: delete FROM activ_princ where id in (select ap.id FROM activ_princ ap inner join sect e on e.id=ap.FK_ID_SECTIUNE_e inner join chest ch on ...
1
vote
1answer
142 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 ...
1
vote
1answer
2k 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 ...
1
vote
2answers
353 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
1answer
2k views

mysqldump: replace drop table in output with delete?

I am using mysqldump to dump some records in a table: mysqldump mydatabase mytable --where="field=value" this emits script that includes drop table mytable statement. Since I use the --where ...
1
vote
1answer
834 views

Large table delete in mysql

I want to delete a table with 10,000,000 records. Due to some restrictions I can't do truncate on that table (I don't have the DROP privilege), I can think of two options: DELETE QUICK To set some ...
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` ...
1
vote
1answer
230 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 ...
1
vote
1answer
976 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
823 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
20 views

Configure MariaDB to log row deletion ONLY

I'm investigating some rows/tables that intermittently go missing on a Magento site, and of course there's no entries in the error log. (I suspect Magento queries/rules or human error might be the ...
1
vote
3answers
39 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
153 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
390 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
145 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 ...
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
540 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
301 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
25 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...
1
vote
1answer
32 views

Delete Query Issue in MySQL and PHPMyAdmin [closed]

I am facing a strange issue with MySQl. I am using PHP and MySQL to built an app. When I am trying to delete records from the table it runs successfully but no record is deleted from the database. ...
1
vote
1answer
125 views

MYSQL - FORCE INDEX ON A DELETE

UPDATE backup.records FORCE INDEX(IDX_records_deviceId) SET readyToBeDeleted = 1 WHERE deviceId = 226219 AND timestamp <= 1445255546 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space ...
1
vote
0answers
10 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
109 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
133 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
70 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
183 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
65 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
79 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
321 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
45 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
94 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
214 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
606 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
2answers
4k 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
802 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
2answers
69 views

Delete “Previous” row, if a value in a column in both rows are the same

Hi I'm new to SQL so bear with me. I am essentially trying to code a report that would give me the greatest number of concurrent events. For example, peak usage of phone calls. I have a column that ...
0
votes
2answers
121 views

what is the quickest way to delete duplicate rows?

I need to delete duplicate rows from a large table. what is the best way to achieve that? currently I use this algorithm: declare @t table ([key] int ) insert into @t select 1 insert into @t ...
0
votes
3answers
392 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
245 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
1k 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
212 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
40 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
86 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
875 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
183 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
637 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
146 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
24 views

MySQL conditional trigger delete

I have a few tables (6 to be exact) and I want to make a trigger that will delete a row from a table based on a value from the main table. To elaborate, Let's say I have a main table called "main" ...