How to Delete Multiple Rows with Different Ids

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 (?,?,?,?,?,?,?,?)

Delete multiple rows by id in SQL

Use IN operator.

Example:

DELETE FROM `ixml_prd_map` WHERE `ixml_prd_map`.`id` IN ("B14260V", "B14261", "B14261V")

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 from a table with a single query when the row ids are not consecutive

You can use IN clause for this.
Sample query may look like below

DELETE FROM table WHERE id IN (1, 4, 6, 7)

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.

Delete multiple rows and keep only one for each id

I can see it working by making another table and copying into that table just the rows you want. This is a tested solution.

CREATE TABLE mytable (id,name);

INSERT INTO mytable
    (`id`, `name`)
VALUES
    ('1','Test1'),
    ('1','Test1'),
    ('2','Test1'),
    ('2','Test1'),
    ('2','Test1'),
    ('3','Test1')

CREATE TABLE mytable2 (id,name);

INSERT INTO mytable2 
    select m2.id, m2.name 
    from mytable m2
    group by m2.id || m2.name
    having count(m2.id || m2.name) >= 1

Desired resultset:

>>> conn.execute('select * from mytable2;').fetchall()
[(u'1', u'Test1'), (u'2', u'Test1'), (u'3', u'Test1')]

SQL Server : delete multiple rows with multiple conditions

There are several things to consider here. When you deploy another variant of a SQL string you're not only generating a new string (allocations in your application), but also query plans on the SQL server. Each permutation has its own plan and cache. This won't likely matter at small scale, but worth keeping in mind.

Often the cleanest way most things is the simplest. To do a small number of items, keeping it simple generally means a loop. Dapper accepts an IEnumerable in the parameters and when it finds an enumerable and it'll execute the same command with the different parameters n times. Though this is round-trips to the server and isn't great for huge numbers, it's very clean. Here's what it'd look like, assuming:

public class Foo
{
    public string Name { get; set; }
    public string Address { get; set; }
}

Then you'd delete multiple like this:

var toDelete = new List<Foo>() {
    new Foo { Name = "Bob", Address = "1600 Pennsylvania Avenue, Washington DC" },
    new Foo { Name = "Maria", Address = "21B Baker St, London, England." }
};

conn.Execute(@"Delete From MyTable Where Name = @Name And Address = @Address", toDelete);

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

---

Related Topics