How to Delete Multiple Rows in SQL Where Id = (X to Y)

How can I delete multiple rows from a table with another condition?

I would recommend using aggregation or something similar before joining:

delete t from test t join
       (select t.name, count(*) as cnt
        from test t
        group by t.name
       ) tt
       on t.name = tt.name
where tt.cnt > 1 and t.product_id is null;

This is much better than a self join without aggregation. Why? Because each row is identified exactly once. In your sample data, a self-join without aggregation attempts to delete row id = 2 twice (once for the match to 1 and once for the match to 3). That is unnecessary. And it can become highly inefficient if a name has many rows.

I also think that you don't simply want a cnt of 2 but you want a non-NULL product id. That is:

delete t from test t join
       (select t.name, count(*) as cnt
        from test t
        where product_id is not null
        group by t.name
       ) tt
       on t.name = tt.name
where tt.cnt >= 1 and t.product_id is null;

Here is a db<>fiddle.

How do I delete multiple rows with different IDs?

If you have to select the id:

 DELETE FROM table WHERE id IN (SELECT id FROM somewhere_else)

If you already know them (and they are not in the thousands):

 DELETE FROM table WHERE id IN (?,?,?,?,?,?,?,?)

SQL Delete Records within a specific Range

If you use Sql Server

delete from Table where id between 79 and 296

Note : the between statement is inclusive, so rows 79 and 296 will also be deleted

After your edit : you now clarified that you want :

ID (>79 AND < 296)

So use this :

delete from Table where id > 79 and id < 296

Delete many rows from a table using id in Mysql

The best way is to use IN statement :

DELETE from tablename WHERE id IN (1,2,3,...,254);

You can also use BETWEEN if you have consecutive IDs :

DELETE from tablename WHERE id BETWEEN 1 AND 254;

You can of course limit for some IDs using other WHERE clause :

DELETE from tablename WHERE id BETWEEN 1 AND 254 AND id<>10;

delete multiple rows of a table where this data came from another select in postgres

Try this:

delete from anotherTable 
        where movieid in 
             (SELECT movieid FROM genres WHERE genre ='Action')

See if this helps

how to delete multiple records from a table (getting multiple values in input)

You may extract the ids from your json string as an array and delete those using ANY operator

WITH t AS 
( 
  SELECT '{"INPUT":{"ID":"2200038,2200039,2200073,2200019"}}' AS input 
) 
DELETE FROM   mytable 
   WHERE  id = ANY ( SELECT unnest(
                          String_to_array(input::json->'INPUT'->>'ID',',')::int[]) 
                  FROM   t );

Demo

Here's a demo using a Bind variable for input in psql. Note that UNNEST was not needed here.

\set input '{"INPUT":{"ID":"2200038,2200039,2200073,2200019"}}'
knayak=# DELETE FROM  mytable WHERE
          id = ANY( String_to_array(:'input'::json->'INPUT'->>'ID',',')::int[] )
DELETE 2

How to delete all rows with type y that also contain an entry in the same table with type x?

During an Update clause on a particular table, MySQL does not allow you to use the same table as a "source" for the subquery in the WHERE condition.

However, you don't need to use a subquery here, and a simple "Self-Inner-Join" would suffice.

DELETE u1 FROM `user` AS u1 
JOIN `user` AS u2 
  ON u2.`user` = u1.`user` AND 
     u2.`type` = 'x'
WHERE u1.type = 'y';

---

Related Topics