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
12
votes
2 answers

Use a CTE to UPDATE or DELETE in MySQL

The new version of MySQL, 8.0, now supports Common Table Expressions. According to the manual: A WITH clause is permitted at the beginning of SELECT, UPDATE, and DELETE statements: WITH ... SELECT ... WITH ... UPDATE ... WITH ... DELETE ... So, I…
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98
12
votes
5 answers

I want a trigger to DELETE from 2 tables in MySQL

I have 3 MySQL tables (food, apple, and orange). I want to delete rows from: apple(idapple, iduser, name) orange(idornge, iduser, name) When deleting a row in food(iduser, name) using one trigger? Here is my trigger so far: CREATE TRIGGER…
Alexander Shlenchack
  • 3,779
  • 6
  • 32
  • 46
12
votes
1 answer

Postgres slow running delete query

We have a table which has just over 62k rows. We are running a very simple delete query on it which takes 45 minutes to complete: DELETE FROM myTable WHERE createdtime < '2017-03-07 05:00:00.000' Things we have tried: 1- Added an index on timestamp…
infiniteLoop
  • 123
  • 1
  • 1
  • 6
12
votes
3 answers

Deleting many rows without locking them

In PostgreSQL I have a query like the following which will delete 250k rows from a 1m row table: DELETE FROM table WHERE key = 'needle'; The query takes over an hour to execute and during that time, the affected rows are locked for writing. That is…
Björn Lindqvist
  • 19,221
  • 20
  • 87
  • 122
12
votes
3 answers

MySQL, delete and index hint

I have to delete about 10K rows from a table that has more than 100 million rows based on some criteria. When I execute the query, it takes about 5 minutes. I ran an explain plan (the delete query converted to select * since MySQL does not support…
Manuel Darveau
  • 4,585
  • 5
  • 26
  • 36
12
votes
2 answers

SQLAlchemy: How to Delete with join

I have trouble doing such thing bottom with SQLAlchemy: DELETE a FROM a INNER JOIN b ON b.`aId` = a.`Id` WHERE `b`.`xxx` = ?; As the post here: SQLAlchemy: Create delete query using self-join on MySQL I've got it's hard to do delete in SQLAlchemy…
jixiang
  • 400
  • 1
  • 2
  • 10
12
votes
1 answer

SQLite, check if TEXT field has any alphabetical chars in it

Okay, so I have a huge list of entries, and in one of the columns (for simplicity let's call it num there's a number, something like 123456780000 (they are all the same length and format), but sometimes there are fields that look something like this…
user3195734
  • 145
  • 2
  • 7
12
votes
3 answers

MySQL: Foreign key constraints that exceed max depth

I have MySQL Server 5.1.62 installed on production server. I am monitoring mysql server's error log file every day and suddenly I found below error in my error log file. InnoDB: Cannot delete/update rows with cascading foreign key constraints that…
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
12
votes
3 answers

"The total number of locks exceeds the lock table size" Deleting 267 Records

I'm trying to delete 267 records out of about 40 million. The query looks like: delete from pricedata where pricedate > '20120413' pricedate is a char(8) field. I know about adjusting innodb_buffer_pool_size, but if I can do select from…
davej
  • 1,350
  • 5
  • 17
  • 34
11
votes
5 answers

SELECT then immediately DELETE mysql record

I have a PHP script that runs a SELECT query then immediately deletes the record. There are multiple machines that are pinging the same php file and fetching data from the same table. Each remote machine is running on a cron job. My problem is that…
john
  • 1,330
  • 3
  • 20
  • 34
11
votes
3 answers

MySQL DELETE all but latest X records

I have a script that runs every hour on my php site. In that script I would like some kind of MySQL query to delete every record from a table but say the latest 50. How would I do something like that? // pseudo code: like this? DELETE from chat…
user1022585
  • 13,061
  • 21
  • 55
  • 75
11
votes
4 answers

Deleting a row based on the max value

How can I structure a mySQL query to delete a row based on the max value. I tried WHERE jobPositonId = max(jobPostionId) but got an error?
Robert de Klerk
  • 624
  • 3
  • 12
  • 24
11
votes
1 answer

Postgres Materialize causes poor performance in delete query

I have a DELETE query that I need to run on PostgreSQL 9.0.4. I am finding that it is performant until it hits 524,289 rows in a subselect query. For instance, at 524,288 there is no materialized view used and the cost looks pretty good: explain…
cizer
  • 213
  • 2
  • 7
11
votes
6 answers

Java sql delete row

Hello I am trying to delete a row from my database. I am getting no errors but it is doing nothing, any help or advice would be great! public static void DeleteRow(String name) { try { Class.forName("com.mysql.jdbc.Driver"); …
user3268379
  • 151
  • 1
  • 1
  • 7
11
votes
4 answers

How do you enable LIMIT for DELETE in SQLite?

Using PHP, I have a simple database that may store multiple items with the same content. I want to delete the first occurrence of an instance when I use DELETE. How do you enable LIMIT for DELETE in SQLite using PHP?
boo-urns
  • 10,136
  • 26
  • 71
  • 107
1 2 3
…
99 100