I want to **delete** using `INNER JOIN` in **SQL Server 2008**.

But I get this error:

&gt; Msg 156, Level 15, State 1, Line 15    
&gt; **Incorrect** syntax near the **keyword** &#39;INNER&#39;.

My code:

    DELETE 
    FROM WorkRecord2 
    INNER JOIN Employee 
            ON EmployeeRun=EmployeeNo
    WHERE Company = &#39;1&#39; 
        AND Date = &#39;2013-05-06&#39;


How can I delete using INNER JOIN with SQL Server?

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 
           story_category ON category_id=category.id);

I tried to delete them executing:

    DELETE FROM story_category 
    WHERE category_id NOT IN (
        SELECT DISTINCT category.id 
        FROM category 
          INNER JOIN story_category ON category_id=category.id);

But I get the next error: 

&gt; \#1093 - You can&#39;t specify target table &#39;story_category&#39; for update in FROM clause 

How can I overcome this?


MySQL Error 1093 - Can&#39;t specify target table for update in FROM clause

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 want to be left with the following after the duplicate removal:

    john  1    1    1    1    1    1
    sally 2    2    2    2    2    2

I&#39;ve tried a few queries but I think they depend on having a row id as I don&#39;t get the desired result. For example:

    DELETE
    FROM table
    WHERE col1 IN (
        SELECT id
        FROM table
        GROUP BY id
        HAVING (COUNT(col1) &gt; 1)
    )

How to delete duplicate rows in SQL Server?

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, site_id, location, id, count( * ) 
    FROM jobs
    GROUP BY site_id, company, title, location
    HAVING count( * ) &gt;1

After running this query, I can remove duplicates using a server side script.

But, I want to know if this can be done only using SQL query.

 

Remove duplicate rows in MySQL

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&#39; deadlines must be deleted from the other table.

I can easily `SELECT` the jobs/deadlines that meets my criteria with a `LEFT JOIN`:

    SELECT * FROM `deadline`
    LEFT JOIN `job` ON deadline.job_id = job.job_id
    WHERE `status` = &#39;szamlazva&#39;
    OR `status` = &#39;szamlazhato&#39;
    OR `status` = &#39;fizetve&#39;
    OR `status` = &#39;szallitva&#39;
    OR `status` = &#39;storno&#39;

(`status` belongs to `job` table not `deadline`)

But when I&#39;d like to delete these rows from `deadline`, MySQL throws an error. My query is:

    DELETE FROM `deadline`
    LEFT JOIN `job`
    ON deadline.job_id = job.job_id
    WHERE `status` = &#39;szamlazva&#39;
    OR `status` = &#39;szamlazhato&#39;
    OR `status` = &#39;fizetve&#39;
    OR `status` = &#39;szallitva&#39;
    OR `status` = &#39;storno&#39;

MySQL error says nothing:

&gt;You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;LEFT JOIN `job` ON deadline.job_id = job.job_id WHERE `status` = &#39;szaml&#39; at line 1

How can I turn my `SELECT` into a working `DELETE` query?

Deleting rows with MySQL LEFT 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 &quot;connect&quot; the tables.
I have tried this script but it doesn&#39;t work.

I have tried this:

    DELETE s FROM spawnlist s
    INNER JOIN npc n ON s.npc_templateid = n.idTemplate
    WHERE (n.type = &quot;monster&quot;);



SQL DELETE with INNER JOIN

How to delete all the records in SQL Server 2008?

Delete all the records

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 
    a1,a2,a3
    from [mytab]

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

I am trying to delete from a few tables at once. I&#39;ve done a bit of research, and came up with this

    DELETE FROM `pets` p,
                `pets_activities` pa
          WHERE p.`order` &gt; :order
            AND p.`pet_id` = :pet_id
            AND pa.`id` = p.`pet_id`

However, I am getting this error

&gt; Uncaught Database_Exception [ 1064 ]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;p, `pets_activities` pa...

I&#39;ve never done a cross table delete before, so I&#39;m inexperienced and stuck for now!

What am I doing wrong?

How to delete from multiple tables in MySQL?

Is it possible to create/delete different databases in the graph database [Neo4j][1] 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` or `rm`?


  [1]: http://neo4j.org/

How to delete/create databases in Neo4j?

I need to do this

    DELETE FROM konta WHERE taken != &#39;&#39;

But != doesn&#39;t exist in mysql.
Anyone know how to do this?

MySQL WHERE: how to write &quot;!=&quot; or &quot;not equals&quot;?

I am trying to run a SQL query to delete rows with id&#39;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&#39;s of rows at a time, Is there any query similar to above method
I am also trying to use this kind of query but failed to execute it

        DELETE FROM `table` WHERE id IN (SELECT * FROM table WHERE id = )

Please tell me the query to do the above action...

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

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 != 1015)
        WHERE th1.parent = 1015
    );

As you can probably tell, I want to delete the parent relation to 1015 if the same tid has other parents. However, that yields me a syntax error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;AS th
    WHERE th.parent = 1015 AND th.tid IN (
      SELECT DISTINCT(th1.tid)
      FROM ter&#39; at line 1


I have checked the documentation, and run the subquery by itself, and it all seems to check out. Can anyone figure out what&#39;s wrong here?

**Update**: As answered below, MySQL does not allow the table you&#39;re deleting from be used in a subquery for the condition.

MySQL DELETE FROM with subquery as condition

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&#39;t work :)

    delete from table order by id ASC limit ((select count(*) from table ) - N)

Thanks.

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

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) 
    WHERE (LENGTH(q.memotext) NOT IN (8,9,10) 
    OR q.memotext NOT LIKE &#39;%/%/%&#39;)
    AND (u.FldFormat = &#39;Date&#39;)
    ;

This is the `DELETE` statement I have come up with:

    DELETE FROM tableA
    WHERE (SELECT q.entitynum
    FROM tableA q
    INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = q.fieldnum) 
    WHERE (LENGTH(q.memotext) NOT IN (8,9,10) 
    OR q.memotext NOT LIKE &#39;%/%/%&#39;)
    AND (u.FldFormat = &#39;Date&#39;))
    ;

I continuously get this error when I try to run this statement:

    ERROR IN SCRIPT: poQuery: Error 7200:  AQE Error:  State = S0000;   NativeError = 2124;
    [iAnywhere Solutions][Advantage SQL Engine]Invalid operand for operator: = Boolean value
    cannot be operated with non-Boolean value.

I have also tried this statement:

    DELETE FROM tableA 
    INNER JOIN tableB u on (u.qlabel = tableA.entityrole AND u.fieldnum = tableA.fieldnum) 
    WHERE (LENGTH(q.memotext) NOT IN (8,9,10) 
    OR tableA.memotext NOT LIKE &#39;%/%/%&#39;)
    AND (u.FldFormat = &#39;Date&#39;)
    ;

Which results in:

    ERROR IN SCRIPT: poQuery: Error 7200:  AQE Error:  State = 42000;   NativeError = 2117;
    [iAnywhere Solutions][Advantage SQL Engine] Unexpected token: INNER -- Expecting semicolon.
    -- Location of error in the SQL statement is: 23 (line: 2 column: 1)

Could someone aid me in properly constructing a DELETE query that will result in the proper data being removed? 


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

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 single delete query to kill them all. The problem is that trying to delete this many rows in one shot locks up the table for a long time, which I would like to avoid if possible. The only ways I can see to get rid of these rows, without taking down the site (by locking up the table) are:

 1. Write a script that will execute thousands of smaller delete queries in a loop. This will theoretically get around the locked table issue because other queries will be able to make it into the queue and run in between the deletes. But it will still spike the load on the database quite a bit and will take a long time to run.
 2. Rename the table and recreate the existing table (it&#39;ll now be empty). Then do my cleanup on the renamed table. Rename the new table, name the old one back and merge the new rows into the renamed table. This is way takes considerably more steps, but should get the job done with minimal interruption. The only tricky part here is that the table in question is a reporting table, so once it&#39;s renamed out of the way and the empty one put in its place all historic reports go away until I put it back in place. Plus the merging process could be a bit of a pain because of the type of data being stored. Overall this is my likely choice right now.

I was just wondering if anyone else has had this problem before and, if so, how you dealt with it without taking down the site and, hopefully, with minimal if any interruption to the users? If I go with number 2, or a different, similar, approach, I can schedule the stuff to run late at night and do the merge early the next morning and just let the users know ahead of time, so that&#39;s not a huge deal. I&#39;m just looking to see if anyone has any ideas for a better, or easier, way to do the cleanup.

Deleting millions of rows in MySQL

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? I have an SQL statement which finds the duplicates and deletes them, but it is taking forever to run. Is there another way to solve this problem? Maybe backing up the table, then restoring after the constraint is added?

How to delete duplicate entries?

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 running with only 297268 rows deleted. (I had to select 100 id&#39;s from an ID table, delete where IN that list, delete from ids table the 100 I selected).

I tried:

    DELETE FROM tbl WHERE id IN (select * from ids)

That&#39;s taking forever, too. Hard to gauge how long, since I can&#39;t see it&#39;s progress till done, but the query was still running after 2 days.

Just kind of looking for the most effective way to delete from a table when I know the specific ID&#39;s to delete, and there are millions of IDs.

Best way to delete millions of rows by ID

Say I have a simple table that has the following fields:

 1. ID: int, autoincremental (identity), primary key
 2. Name: varchar(50), unique, has unique index
 3. Tag: int

I never use the ID field for lookup, because my application is always based on working with the Name field. 

I need to change the Tag value from time to time. I&#39;m using the following trivial SQL code: 

    UPDATE Table SET Tag = XX WHERE Name = YY;

I wondered if anyone knows whether the above is always faster than:

    DELETE FROM Table WHERE Name = YY;
    INSERT INTO Table (Name, Tag) VALUES (YY, XX);

Again - I know that in the second example the ID is changed, but it does not matter for my application.



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

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 delete 254 entries,so this method is going to take hours,how can i tell mysql to delete rows that i specify,coz i want to skip deleting some entries out of this 254.

Deleting whole table and importing needed entries is not an option.


Delete many rows from a table using id in Mysql

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 id_guide_category 
                                       FROM guide_category AS gc
                                  LEFT JOIN guide AS g ON g.id_guide = gc.id_guide
                                      WHERE g.title IS NULL)

Error:  
&gt;You can&#39;t specify target table &#39;guide_category&#39; for update in FROM clause

SQL DELETE with JOIN another table for WHERE condition

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&gt; delete from student where id=1;
    Usage: delete [FILE|JAR|ARCHIVE] &lt;value&gt; [&lt;value&gt;]*
    Query returned non-zero code: 1, cause: null


How to delete and update a record in Hive

I have a java EE project using JPA (transaction-type=&quot;JTA&quot;), 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 Serializable {
    
    	@PersistenceContext(unitName = &quot;dtdJpa&quot;)
    	private EntityManager entityManager;
    
    	@TransactionAttribute(TransactionAttributeType.REQUIRED)
    	public Object updateObject(Object object) {
    		object = entityManager.merge(object);
    		return object;
    	}
    
    	@TransactionAttribute(TransactionAttributeType.REQUIRED)
    	public void createObject(Object object) {
    		entityManager.persist(object);
    	}
    
    	public void refresh(Object object) {
    		entityManager.refresh(object);
    	}
    
    	public &lt;T&gt; T find(Class&lt;T&gt; clazz, Long id) {
    		return entityManager.find(clazz, id);
    	}
    
    	@TransactionAttribute(TransactionAttributeType.REQUIRED)
    	public void deleteObject(Object object) {
    		entityManager.remove(object);
    	}
    }

but when I invoke `deleteObject`, this exception comes out.

&gt; java.lang.IllegalArgumentException: Removing a detached instance com.test.User#5

How is this caused and how can I solve it?

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

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 differences between the two.



Pros &amp; Cons of TRUNCATE vs DELETE FROM

I tried to truncate a table with foreign keys and got the message:

&gt; &quot;**Cannot truncate table  because it is being referenced by a FOREIGN KEY constraint**&quot;. 

I read a lot of literature about the problem and thought that I found the solution by using delete 

    DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

But I still got an error message:

&gt; &quot;**The DELETE statement conflicted with the REFERENCE constraint**&quot;. 

When I try to delete with Microsoft Management Studio and execute the previous query 
    
    DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)

it doesn&#39;t give an error and works properly. I want to delete all information from a table and add new into it, but I don&#39;t want to drop and create foreign keys.



I got error &quot;The DELETE statement conflicted with the REFERENCE constraint&quot;

I&#39;m a relative novice when it comes to databases.  We are using MySQL and I&#39;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&#39;t find one.

The goal is to remove all the rows in table A that have a matching id in table B.

I&#39;m currently doing the following:

    DELETE FROM a WHERE EXISTS (SELECT b.id FROM b WHERE b.id = a.id);

There are approximately 100K rows in table a and about 22K rows in table b.  The column &#39;id&#39; is the PK for both tables.

This statement takes about 3 minutes to run on my test box - Pentium D, XP SP3, 2GB ram, MySQL 5.0.67.  This seems slow to me.  Maybe it isn&#39;t, but I was hoping to speed things up.  Is there a better/faster way to accomplish this?

&lt;hr&gt;

EDIT:

Some additional information that might be helpful.  Tables A and B have the same structure as I&#39;ve done the following to create table B:

    CREATE TABLE b LIKE a;

Table a (and thus table b) has a few indexes to help speed up queries that are made against it.  Again, I&#39;m a relative novice at DB work and still learning.  I don&#39;t know how much of an effect, if any, this has on things.  I assume that it does have an effect as the indexes have to be cleaned up too, right?  I was also wondering if there were any other DB settings that might affect the speed.

Also, I&#39;m using INNO DB.

&lt;hr&gt;

Here is some additional info that might be helpful to you.

Table A has a structure similar to this (I&#39;ve sanitized this a bit):

    DROP TABLE IF EXISTS `frobozz`.`a`;
    CREATE TABLE  `frobozz`.`a` (
      `id` bigint(20) unsigned NOT NULL auto_increment,
      `fk_g` varchar(30) NOT NULL,
      `h` int(10) unsigned default NULL,
      `i` longtext,
      `j` bigint(20) NOT NULL,
      `k` bigint(20) default NULL,
      `l` varchar(45) NOT NULL,
      `m` int(10) unsigned default NULL,
      `n` varchar(20) default NULL,
      `o` bigint(20) NOT NULL,
      `p` tinyint(1) NOT NULL,
      PRIMARY KEY  USING BTREE (`id`),
      KEY `idx_l` (`l`),
      KEY `idx_h` USING BTREE (`h`),
      KEY `idx_m` USING BTREE (`m`),
      KEY `idx_fk_g` USING BTREE (`fk_g`),
      KEY `fk_g_frobozz` (`id`,`fk_g`),
      CONSTRAINT `fk_g_frobozz` FOREIGN KEY (`fk_g`) REFERENCES `frotz` (`g`)
    ) ENGINE=InnoDB AUTO_INCREMENT=179369 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

I suspect that part of the issue is there are a number of indexes for this table.
Table B looks similar to table B, though it only contains the columns `id` and `h`.


Also, the profiling results are as follows:

    starting 0.000018
    checking query cache for query 0.000044
    checking permissions 0.000005
    Opening tables 0.000009
    init 0.000019
    optimizing 0.000004
    executing 0.000043
    end 0.000005
    end 0.000002
    query end 0.000003
    freeing items 0.000007
    logging slow query 0.000002
    cleaning up 0.000002


&lt;hr&gt;

**SOLVED**

Thanks to all the responses and comments.  They certainly got me to think about the problem.  Kudos to **dotjoe** for getting me to step away from the problem by asking the simple question &quot;Do any other tables reference a.id?&quot;

The problem was that there was a DELETE TRIGGER on table A which called a stored procedure to update two other tables, C and D.  Table C had a FK back to a.id and after doing some stuff related to that id in the stored procedure, it had the statement,

    DELETE FROM c WHERE c.id = theId;

I looked into the EXPLAIN statement and rewrote this as,

    EXPLAIN SELECT * FROM c WHERE c.other_id = 12345;

So, I could see what this was doing and it gave me the following info:

    id            1
    select_type   SIMPLE
    table         c
    type          ALL
    possible_keys NULL
    key           NULL
    key_len       NULL
    ref           NULL
    rows          2633
    Extra         using where

This told me that it was a painful operation to make and since it was going to get called 22500 times (for the given set of data being deleted), that was the problem.  Once I created an INDEX on that other_id column and reran the EXPLAIN, I got:

    id            1
    select_type   SIMPLE
    table         c
    type          ref
    possible_keys Index_1
    key           Index_1
    key_len       8
    ref           const
    rows          1
    Extra         

Much better, in fact really great.

I added that Index_1 and my delete times are in line with the times reported by **mattkemp**.  This was a really subtle error on my part due to shoe-horning some additional functionality at the last minute.  It turned out that most of the suggested alternative DELETE/SELECT statements, as **Daniel** stated, ended up taking essentially the same amount of time and as **soulmerge** mentioned, the statement was pretty much the best I was going to be able to construct based on what I needed to do.  Once I provided an index for this other table C, my DELETEs were fast.

**Postmortem**:  
Two lessons learned came out of this exercise.  First, it is clear that I didn&#39;t leverage the power of the EXPLAIN statement to get a better idea of the impact of my SQL queries.  That&#39;s a rookie mistake, so I&#39;m not going to beat myself up about that one.  I&#39;ll learn from that mistake.  Second, the offending code was the result of a &#39;get it done quick&#39; mentality and inadequate design/testing led to this problem not showing up sooner.  Had I generated several sizable test data sets to use as test input for this new functionality, I&#39;d have not wasted my time nor yours.  My testing on the DB side was lacking the depth that my application side has in place.  Now I&#39;ve got the opportunity to improve that.  

[Reference:  EXPLAIN Statement][1]

  [1]: http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

Faster way to delete matching rows?

I accidentally deleted some huge number of rows from a table...

How can I roll it back?

I executed the query using [PuTTY][1].

I&#39;ll be grateful if any of you can guide me safely out of this...

  [1]: http://en.wikipedia.org/wiki/PuTTY


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

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 &quot;**You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &#39;LIMIT 1&#39; at line 1**&quot;


The table I put together is called *test*; it has 3 columns, *id*, *name* and *created*. I populated the table with several records and then attempted to delete one. Below is the statement I used to try and accomplish this.

`DELETE t FROM test t WHERE t.name = &#39;foo&#39; LIMIT 1`

Without the use of LIMIT 1, the statement executes just fine, but of course I wouldn&#39;t be using LIMIT if there wasn&#39;t a need for it.

I&#39;m fully aware that I can use another statement to accomplish this DELETE successfully. See below:
`DELETE FROM test WHERE name = &#39;foo&#39; LIMIT 1`

However my question is centered on why the first statement isn&#39;t working with LIMIT.

So my question is, what I have done incorrectly with respect to the first statement to generate this error? 

MySQL LIMIT on DELETE statement

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 including JOIN. 

In my case I have a table `ProductFilters` and another table `Products` joined on fields `ProductFilters.productID = Products.ID`. I want to delete the rows from `ProductFilters` having an `ID` higher or equal to 200 and the product they refer has the name &#39;Mark&#39; (name is a field in Product).

I would like to be informed initially if JOIN is acceptable in a Delete Query in Oracle. If not how should I modify this Query in order to make it work, since on that form I receive an error:

    DELETE From PRODUCTFILTERS pf 
    where pf.id&gt;=200 
    And pf.rowid in 
    (
         Select rowid from PRODUCTFILTERS 
         inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID 
         And PRODUCTS.NAME= &#39;Mark&#39;
    );       

Delete with &quot;Join&quot; in Oracle sql Query

I am looking for a way to return the number of rows affected by a DELETE clause in PostgreSQL. The [documentation][1] states that;

&gt;  On successful completion, a DELETE
&gt; command returns a command tag of the
&gt; form
&gt; 
&gt; DELETE count
&gt; 
&gt; The count is the number of rows
&gt; deleted. If count is 0, no rows
&gt; matched the condition (this is not
&gt; considered an error).
&gt; 
&gt; If the DELETE command contains a
&gt; RETURNING clause, the result will be
&gt; similar to that of a SELECT statement
&gt; containing the columns and values
&gt; defined in the RETURNING list,
&gt; computed over the row(s) deleted by
&gt; the command.

But I am having trouble finding a good example of it. Can anyone help me with this, *how can I find out how many rows were deleted?*


----------
**EDIT:**
I wanted to present an alternative that I have found later. It can be found in [here][2], explained under *38.5.5. Obtaining the Result Status* title.


  [1]: http://www.postgresql.org/docs/8.2/static/sql-delete.html
  [2]: http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html

How to get the number of deleted rows in PostgreSQL?

I want to delete all the rows with timestamp older than 180 days from a specific table in my database.

I&#39;ve tried the this:

 
    DELETE FROM on_search WHERE search_date &lt; DATE_SUB(NOW(), INTERVAL 180 DAY);

But that deleted all the rows and not only the rows older than 6 months. 

I have a column in on_search table called search_date and contains the time when that row was created.

    search_id   search_term    search_date 
    660779      car games      1390052553 

Delete all rows with timestamp older than x days

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 between 79 &amp; 296:

My worry is if I say delete everything with an ID (&gt;79 AND &lt; 296) then it may literally wipe the whole table.

SQL Delete Records within a specific Range

I want to do something like this:

    DELETE FROM table WHERE id IN (SELECT ....)


How can I do that?

How do I delete multiple rows with different IDs?

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` variables - but that isn&#39;t set, e.g. 

    delete 
    from mytable 
    where datefield = &#39;5-Oct-2008&#39; 

    select @@ROWCOUNT 

always returns a 0. 


MSDN suggests the [`OUTPUT`][1] construction, e.g. 

    delete from mytable 
    where datefield = &#39;5-Oct-2008&#39; 
    output datefield into #doomed

    select count(*) 
    from #doomed

this actually fails with a syntax error. 

Any ideas? 


  [1]: http://msdn.microsoft.com/en-us/library/ms189835.aspx



All Sql Delete Solutions on Gang of Coders

Total of 34 Sql Delete Solutions