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
1715
votes
14 answers

How can I delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON…
nettoon493
  • 17,733
  • 7
  • 30
  • 45
816
votes
17 answers

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN …
Sergio del Amo
  • 76,835
  • 68
  • 152
  • 179
568
votes
28 answers

How to delete duplicate rows in SQL Server?

How can I delete duplicate rows where no unique row id exists? My table is col1 col2 col3 col4 col5 col6 col7 john 1 1 1 1 1 1 john 1 1 1 1 1 1 sally 2 2 2 2 2 2 sally 2 2 2 2 2 2 I…
Fearghal
  • 10,569
  • 17
  • 55
  • 97
442
votes
28 answers

Remove duplicate rows in MySQL

I have a table with the following fields: id (Unique) url (Unique) title company site_id Now, I need to remove rows having same title, company and site_id. One way to do it will be using the following SQL along with a script (PHP): SELECT title,…
Chetan
  • 4,885
  • 4
  • 22
  • 15
229
votes
6 answers

Deleting rows with MySQL LEFT JOIN

I have two tables, one for job deadlines, one for describe a job. Each job can take a status and some statuses means the jobs' deadlines must be deleted from the other table. I can easily SELECT the jobs/deadlines that meets my criteria with a LEFT…
fabrik
  • 14,094
  • 8
  • 55
  • 71
150
votes
10 answers

Delete all the records

How to delete all the records in SQL Server 2008?
kaveh
  • 1,509
  • 2
  • 9
  • 3
145
votes
9 answers

How to delete the top 1000 rows from a table using Sql Server 2008?

I have a table in SQL Server. I would like to delete the top 1000 rows from it. However, I tried this, but I instead of just deleting the top 1000 rows it deleted all the rows in the table. Here is the code: delete from [mytab] select top 1000…
edgarmtze
  • 24,683
  • 80
  • 235
  • 386
142
votes
3 answers

SQL DELETE with INNER JOIN

There are 2 tables, spawnlist and npc, and I need to delete data from spawnlsit. npc_templateid = n.idTemplate is the only thing that "connect" the tables. I have tried this script but it doesn't work. I have tried this: DELETE s FROM spawnlist…
JoinOG
  • 1,513
  • 3
  • 11
  • 9
133
votes
7 answers

How to delete from multiple tables in MySQL?

I am trying to delete from a few tables at once. I've done a bit of research, and came up with this DELETE FROM `pets` p, `pets_activities` pa WHERE p.`order` > :order AND p.`pet_id` = :pet_id AND pa.`id` =…
alex
  • 479,566
  • 201
  • 878
  • 984
120
votes
5 answers

How to delete multiple rows in SQL where id = (x to y)

I am trying to run a SQL query to delete rows with id's 163 to 265 in a table I tried this to delete less number of rows DELETE FROM `table` WHERE id IN (264, 265) But when it comes to delete 100's of rows at a time, Is there any query similar…
balu zapps
  • 1,209
  • 2
  • 9
  • 4
112
votes
9 answers

MySQL DELETE FROM with subquery as condition

I am trying to do a query like this: DELETE FROM term_hierarchy AS th WHERE th.parent = 1015 AND th.tid IN ( SELECT DISTINCT(th1.tid) FROM term_hierarchy AS th1 INNER JOIN term_hierarchy AS th2 ON (th1.tid = th2.tid AND th2.parent !=…
mikl
  • 23,749
  • 20
  • 68
  • 89
110
votes
6 answers

How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?

Database: Sybase Advantage 11 On my quest to normalize data, I am trying to delete the results I get from this SELECT statement: SELECT tableA.entitynum FROM tableA q INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum)…
LuiCami
  • 1,241
  • 2
  • 8
  • 8
109
votes
3 answers

MySQL WHERE: how to write "!=" or "not equals"?

I need to do this DELETE FROM konta WHERE taken != '' But != doesn't exist in mysql. Anyone know how to do this?
Posttwo
  • 1,274
  • 2
  • 9
  • 13
108
votes
14 answers

How to delete/create databases in Neo4j?

Is it possible to create/delete different databases in the graph database Neo4j like in MySQL? Or, at least, how to delete all nodes and relationships of an existing graph to get a clean setup for tests, e.g., using shell commands similar to rmrel…
rmv
  • 3,195
  • 4
  • 26
  • 29
108
votes
17 answers

Deleting millions of rows in MySQL

I recently found and fixed a bug in a site I was working on that resulted in millions of duplicate rows of data in a table that will be quite large even without them (still in the millions). I can easily find these duplicate rows and can run a…
Steven Surowiec
  • 10,030
  • 5
  • 32
  • 37
1
2 3
…
99 100