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
60
votes
7 answers

SQL Delete Records within a specific Range

This is probably a very simple question for somebody with experience, but I just wanted to know the safest way to delete a couple of hundred records in an SQL table that fall between a specific range. For example I need to delete rows with an ID…
user964769
60
votes
2 answers

Delete all rows with timestamp older than x days

I want to delete all the rows with timestamp older than 180 days from a specific table in my database. I've tried the this: DELETE FROM on_search WHERE search_date < DATE_SUB(NOW(), INTERVAL 180 DAY); But that deleted all the rows and not only the…
Khaleal
  • 876
  • 3
  • 10
  • 15
57
votes
7 answers

Counting the number of deleted rows in a SQL Server stored procedure

In SQL Server 2005, is there a way of deleting rows and being told how many were actually deleted? I could do a select count(*) with the same conditions, but I need this to be utterly trustworthy. My first guess was to use the @@ROWCOUNT…
Unsliced
  • 10,404
  • 8
  • 51
  • 81
55
votes
4 answers

How do I delete multiple rows with different IDs?

I want to do something like this: DELETE FROM table WHERE id IN (SELECT ....) How can I do that?
ave4496
  • 2,950
  • 3
  • 21
  • 48
50
votes
1 answer

How to delete multiple rows with 2 columns as composite primary key in MySQL?

My innodb table has the following structure: 4 columns (CountryID, Year, %Change, Source), with the 2 columns (CountryID, Year) as the primary key. How do I delete multiple rows other than using a for-loop to delete each row? I'm looking for…
Ana Ban
  • 1,395
  • 4
  • 21
  • 29
50
votes
3 answers

TSQL Try / Catch within Transaction or vice versa?

I'm writing a script that will delete records from a number of tables, but before it deletes it must return a count for a user to confirm before committing. This is a summary of the script. BEGIN TRANSACTION SCHEDULEDELETE BEGIN TRY …
Devasta
  • 1,489
  • 2
  • 17
  • 28
47
votes
5 answers

Delete all but one duplicate record

I have a table that is supposed to keep a trace of visitors to a given profile (user id to user id pair). It turns out my SQL query was a bit off and is producing multiple pairs instead of single ones as intended. With hindsight I should have…
James P.
  • 19,313
  • 27
  • 97
  • 155
47
votes
9 answers

How do I delete all the duplicate records in a MySQL table without temp tables

I've seen a number of variations on this but nothing quite matches what I'm trying to accomplish. I have a table, TableA, which contain the answers given by users to configurable questionnaires. The columns are member_id, quiz_num, question_num,…
MivaScott
  • 1,763
  • 1
  • 12
  • 30
46
votes
4 answers

Delete from one table with join

I'm trying to delete records from one database based on a selection criteria of another. We have two tables, emailNotification which stores a list of jobs and emails. Then we have jobs. I want to clear out emailNotifications for jobs that have been…
Julian Young
  • 872
  • 2
  • 9
  • 21
38
votes
3 answers

hibernate and delete all

What is the best way to delete all rows in a table in Hibernate? If I iterate over a collection and call session.delete() it's not performing to my knowledge. If I use another option session.createQuery("delete ...") it doesn't affect persistence…
feiroox
  • 3,069
  • 8
  • 31
  • 31
38
votes
7 answers

Google BigQuery Delete Rows?

Anyone know of any plans to add support for delete parts of data from a table in Google Bigquery? The issue we have right now is we are using it for analytics of data points we collect over time. We want to run the queries over the last X days of…
Daum
  • 815
  • 1
  • 7
  • 11
34
votes
1 answer

Mysql delete statement with limit

I'm trying to delete rows from a table but I get an error. DELETE FROM `chat_messages` ORDER BY `timestamp` DESC LIMIT 20, 50; I get this error at 50: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server…
SBSTP
  • 3,479
  • 6
  • 30
  • 41
34
votes
3 answers

Postgresql delete multiple rows from multiple tables

Consider 2 or more tables: users (id, firstname, lastname) orders (orderid, userid, orderdate, total) I wish to delete all users and their orders that match first name 'Sam'. In mysql, I usually do left join. In this example userid is unknown to…
fawzib
  • 774
  • 2
  • 9
  • 23
34
votes
11 answers

How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table

Let's say we have table Sales with 30 columns and 500,000 rows. I would like to delete 400,000 in the table (those where "toDelete='1'"). But I have a few constraints : the table is read / written "often" and I would not like a long "delete" to…
Skippy Fastol
  • 1,745
  • 2
  • 17
  • 32
30
votes
4 answers

How to delete in MS Access when using JOIN's?

I am attempting to use the DELETE clause in MS Access and have an issue when also using the JOIN clause. I have notice this can be accomplished by using the DISTINCTROW key word. For example, the following SQL statement does not allow for…
Curtis Inderwiesche
  • 4,940
  • 19
  • 60
  • 82
1 2
3
…
99 100