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

learn more… | top users | synonyms (1)

0
votes
2answers
42 views

TSQL Delete All dates and stores data

This might sound like a stupid question but I can't seem to find a better way. It's a posting system where stores send us info on a daily basis (can be for multiple days). That info is placed in ...
2
votes
1answer
28 views

Cascade deletes from multiple tables

I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating ...
0
votes
0answers
36 views

Deleting a Row in Table- Best way to also delete all rows it affects? [closed]

I have created a very simple SQL script (see below) that deletes rows in my table if a column name is a certain value. DELETE FROM table_name WHERE some_column=some_value; However, this table ...
5
votes
1answer
111 views

Hard delete vs soft delete performance

I've got this table that is intended to have a lot of activity. Anything that's in it indicates that a user action is still pending. Once deleted, it would indicate that action is no longer pending. ...
4
votes
1answer
532 views

Partialy Solved - Problems Deleting Data

I did a workaround that solved the problem for today but I want to know what I did wrong for the next project. I'm stuck on trying to delete some information on my MySQL database. I've never used a ...
0
votes
1answer
25 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" ...
1
vote
1answer
24 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 ...
0
votes
0answers
50 views

Why “0 pages are entirely empty” after mass delete + vacuum?

I have a huge table for logging the user actions (audit log) in my PostgreSQL 9.4 database. It gets about 100 new records per second. I've removed very old records recently. I assume this affected a ...
0
votes
1answer
20 views

How to pass parameters to delete statement?

While executing this PL/SQL block: CREATE OR REPLACE PROCEDURE procAuth_Title_Delete (auth_ID NUMBER, title_Nos IN NUMBER) IS BEGIN DELETE FROM ...
1
vote
1answer
63 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 ...
0
votes
1answer
18 views

Delete with join and condition (3 tables) - MySQL

Need some assistance on MySQL delete using join with condition (3 tables). snote (child table) -------- snote_id bnote_id bnote (parent table of snote) ------ bnote_id note_id note (parent table of ...
1
vote
1answer
36 views

Delete query does not appear to open space on the SQL Server

Our SQL Server (version 2012) is housed on a cloud server, for which we pay whenever we need additional space. We determined that we could delete some of the data to prevent the need for continually ...
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
33 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. ...
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
0answers
34 views

Why is one table taking up ~75% of time for DELETE

I have a table (Period) linked to about 6-7 other tables. When I DELETE a period, a CASCADE DELETE is used to delete the related records in the other tables. Two of the tables (Result, Amount) have ...
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
1answer
126 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
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 ...
4
votes
1answer
134 views

Alter ON DELETE CASCADE in 1 table

I'm really new to ON DELETE CASCADE. How can I delete child comments when the parent comment gets deleted in a table? Table Schema 'CREATE TABLE `comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
5
votes
2answers
105 views

Help optimising delete statement

Using SQL Server 2012 Standard - I'm running a delete on a table based on the contents of another table. It's taking rather a long time (5 hours) and doesn't seem to be optimal to me, would appreciate ...
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 ...
5
votes
3answers
1k views

How can a MySQL delete statement affect a table with over 30 millions records?

I have to delete about 28 million records from a InnoDB table in MySQL. I am wondering what happens if I am doing a select statement on the same table during the delete operation. I guess the delete ...
1
vote
1answer
50 views

Query optimization in case of TIMESTAMPDIFF

I have a table where I need to delete all rows older than 5 hours. Currently I'm doing: delete from my_table where TIMESTAMPDIFF(HOUR, from_unixtime(round(created_on/1000)), now()) > 5 The ...
2
votes
1answer
67 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
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 ...
4
votes
2answers
385 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 ...
1
vote
1answer
110 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 ...
0
votes
3answers
402 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
346 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
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
2answers
134 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
votes
2answers
87 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
309 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
181 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
46 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
57 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
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 ...
6
votes
2answers
326 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
26 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
184 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
43 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
651 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
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 ...
0
votes
0answers
45 views

Mysql - too many Deletion - Insertion

I'm creating cron jobs for my OLAP system. I have a cron job that deletes the records from the summary table first and then insert the records in it again: Say Deleting 1 month records, and ...
3
votes
1answer
118 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
171 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
485 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 ...
1
vote
1answer
158 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
35 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 ...