Stack Overflow
Stack Overflow

Questions tagged [sql-delete]

The SQL DELETE statement allows you to delete a single row or multiple rows from a SQL table.

The DELETE statement removes one or more rows from a table. A subset may be defined for deletion using a condition, otherwise all rows are removed. Some DBMSs, like MySQL, allow to delete rows from multiple tables with one DELETE statement (this is sometimes called multi-table DELETE).

Use this tag instead of which is too broad.

Reference

3082 questions
102
votes
17 answers

SQL query: Delete all records from the table except latest N?

Is it possible to build a single mysql query (without variables) to remove all records from the table, except latest N (sorted by id desc)? Something like this, only it doesn't work :) delete from table order by id ASC limit ((select count(*) from…
serg
  • 109,619
  • 77
  • 317
  • 330
95
votes
8 answers

Best way to delete millions of rows by ID

I need to delete about 2 million rows from my PG database. I have a list of IDs that I need to delete. However, any way I try to do this is taking days. I tried putting them in a table and doing it in batches of 100. 4 days later, this is still…
Anthony Greco
  • 2,885
  • 4
  • 27
  • 39
93
votes
16 answers

How to delete duplicate entries?

I have to add a unique constraint to an existing table. This is fine except that the table has millions of rows already, and many of the rows violate the unique constraint I need to add. What is the fastest approach to removing the offending rows?…
gjrwebber
  • 2,658
  • 2
  • 22
  • 26
89
votes
15 answers

In SQL, is UPDATE always faster than DELETE+INSERT?

Say I have a simple table that has the following fields: ID: int, autoincremental (identity), primary key Name: varchar(50), unique, has unique index Tag: int I never use the ID field for lookup, because my application is always based on working…
Roee Adler
  • 33,434
  • 32
  • 105
  • 133
85
votes
11 answers

Delete many rows from a table using id in Mysql

I am a Linux admin with only basic knowledge in Mysql Queries I want to delete many table entries which are ip address from my table using id, currently i am using DELETE from tablename where id=1; DELETE from tablename where id=2; but i have to…
Kevin Parker
  • 1,350
  • 3
  • 14
  • 18
78
votes
16 answers

How to delete and update a record in Hive

I have installed Hadoop, Hive, Hive JDBC. which are running fine for me. But I still have a problem. How to delete or update a single record using Hive because delete or update command of MySQL is not working in Hive. Thanks hive> delete from…
Charnjeet Singh
  • 3,056
  • 6
  • 35
  • 65
76
votes
4 answers

SQL DELETE with JOIN another table for WHERE condition

I have to delete rows from guide_category that have no relation with guide table (dead relations). Here is what I want to do, but it of course does not work. DELETE FROM guide_category AS pgc WHERE pgc.id_guide_category IN (SELECT…
hsz
  • 148,279
  • 62
  • 259
  • 315
76
votes
5 answers

java.lang.IllegalArgumentException: Removing a detached instance com.test.User#5

I have a java EE project using JPA (transaction-type="JTA"), hibernate as provider. I write my beans to handle the CRUD things. The program running in JBOSS 7 AS. I have an EntityManagerDAO : @Stateful public class EntityManagerDao implements…
neptune
  • 875
  • 1
  • 7
  • 8
70
votes
6 answers

How to get the number of deleted rows in PostgreSQL?

I am looking for a way to return the number of rows affected by a DELETE clause in PostgreSQL. The documentation states that; On successful completion, a DELETE command returns a command tag of the form DELETE count The count is the number of…
Erkan Haspulat
  • 12,032
  • 6
  • 39
  • 45
69
votes
5 answers

I got error "The DELETE statement conflicted with the REFERENCE constraint"

I tried to truncate a table with foreign keys and got the message: "Cannot truncate table because it is being referenced by a FOREIGN KEY constraint". I read a lot of literature about the problem and thought that I found the solution by using…
Peter
  • 691
  • 1
  • 5
  • 3
69
votes
11 answers

Pros & Cons of TRUNCATE vs DELETE FROM

Could someone give me a quick overview of the pros and cons of using the following two statements: TRUNCATE TABLE dbo.MyTable vs DELETE FROM dbo.MyTable It seems like they both do the same thing when all is said and done; but are there must be…
Jim B
  • 8,344
  • 10
  • 49
  • 77
67
votes
7 answers

MySQL LIMIT on DELETE statement

I put together a test table for a error I recently came across. It involves the use of LIMIT when attempting to delete a single record from a MySQL table. The error I speak of is "You have an error in your SQL syntax; check the manual that…
Andre
  • 1,347
  • 3
  • 14
  • 23
64
votes
5 answers

Delete with "Join" in Oracle sql Query

I am not deeply acquainted with Oracle Sql Queries, therefore I face a problem on deleting some rows from a table which must fulfill a constraint which includes fields of another (joining) table. In other words I want to write a query to delete rows…
arjacsoh
  • 8,932
  • 28
  • 106
  • 166
61
votes
14 answers

Faster way to delete matching rows?

I'm a relative novice when it comes to databases. We are using MySQL and I'm currently trying to speed up a SQL statement that seems to take a while to run. I looked around on SO for a similar question but didn't find one. The goal is to remove…
itsmatt
  • 31,265
  • 10
  • 100
  • 164
61
votes
10 answers

How can I roll back my last delete command in MySQL?

I accidentally deleted some huge number of rows from a table... How can I roll it back? I executed the query using PuTTY. I'll be grateful if any of you can guide me safely out of this...
Vijay
  • 5,331
  • 10
  • 54
  • 88
1
2
3
…
99 100