Making select and delete queries as single statement

Question

First query

    select id from posts where post_title='abc' || post_title='xyz' order by id desc limit 1;

Let's say the returned values are 730 and 735.

Next query

    delete from posts where id in(730,735);

I want both of these queries to be combined into one statement. How can it be done. Please help

I have tried this one below. it doesn't work.

delete from posts where id in
        (
            select id from posts where post_title='abc' order by id desc limit 1,
                            select id from posts where post_title='xyz' order by id desc limit 1
                        );

i want to delete only one entry which is the id with higher value. — Krish Gowda, Dec 26 '13 at 10:43

Sohail · Answer 1 · 2013-12-26T11:10:53.497

2

In my opinion, We must use IF-EXISTS clause when executing delete-and-select in one query, because if select returns null, it will throw an exception so try this:

IF EXISTS (SELECT id FROM [Posts] WHERE post_title IN ('abc', 'xyz'))
BEGIN 
     DELETE FROM posts 
     WHERE id IN (SELECT id 
                  FROM [Posts]
                  WHERE post_title IN ('abc', 'xyz') 
                  ORDER BY post_title, id DESC
                 ) 
END

edited Dec 26 '13 at 11:10

answered Dec 26 '13 at 11:02

Sohail

574
3
21

one up for the suggestion of using if exists. but i wanted only one row returned per one match of post_title value. – Krish Gowda Dec 26 '13 at 11:23
use MIN() and MAX() then, as @Saharsh suggested. – Sohail Dec 26 '13 at 11:28

score 1 · Accepted Answer · answered Dec 26 '13 at 10:42

1

Try this:

DELETE FROM posts 
WHERE id IN (SELECT id 
             FROM (SELECT post_title, MAX(id) id 
                   FROM posts 
                   WHERE post_title IN ('abc', 'xyz') 
                   GROUP BY post_title 
                  ) A 
            )

OR

DELETE FROM posts 
WHERE id IN (SELECT id 
             FROM (SELECT post_title, id 
                   FROM posts 
                   WHERE post_title IN ('abc', 'xyz') 
                   ORDER BY post_title, id DESC
                 ) A 
            GROUP BY post_title)

answered Dec 26 '13 at 10:42

Saharsh Shah

28,687
8
48
83

i am trying to delete duplicate entries. so i want only one row returned with highest id per match. – Krish Gowda Dec 26 '13 at 10:57
@KrishGowda You will get latest(only one) entry of posts based on post title specified in where condition – Saharsh Shah Dec 26 '13 at 10:58
@KrishGowda You're most welcome. You can also upvote any question and answer if you find that it is helpful to you by clicking on up arrow above the right mark – Saharsh Shah Dec 26 '13 at 11:02

Aziz Shaikh · Answer 3 · 2013-12-26T10:41:28.863

0

Try this:

delete from posts where id in (
    select * from
    (
       select id from posts 
       where post_title='abc' or post_title='xyz'
    ) as t
);

You have to wrap it in an alias.

edited Dec 26 '13 at 10:41

answered Dec 26 '13 at 10:35

Aziz Shaikh

16,245
11
62
79

@KrishGowda I have updated the query above and have tested it too. Please try again. – Aziz Shaikh Dec 26 '13 at 10:46
i am trying to delete duplicate entries. so i want only one row returned with highest id per match. – Krish Gowda Dec 26 '13 at 10:58

Rubin Porwal · Answer 4 · 2013-12-26T11:06:49.247

0

As a solution to your problem please try executing following sql query

delete FROM `posts` WHERE  id in 
(select p.id from 
                 (select id from posts where title in('abc','xyz') p)

edited Dec 26 '13 at 11:06

answered Dec 26 '13 at 10:43

Rubin Porwal

3,736
1
23
26

i am trying to delete duplicate entries. so i want only one row returned with highest id per match. – Krish Gowda Dec 26 '13 at 10:57

score 0 · Answer 5 · edited May 23 '17 at 11:45

As explained here

In MySQL, you can't modify the same table which you use in the SELECT part.

If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance:

delete from posts where id in
(
  select id from (select * from posts)as x 
  where post_title = 'xyz' or
        post_title = 'abc'
  order by id desc
);

see here in action

just add limit 1 in your actual code, not supported by the online version.

score 0 · Answer 6 · edited Dec 26 '13 at 11:09

0

Try this

DELETE FROM [posts]
WHERE id = ANY( SELECT id FROM [posts] WHERE post_title IN('abc','xyz') );

edited Dec 26 '13 at 11:09

Sohail

574
3
21

answered Dec 26 '13 at 11:05

Ajitha Ms

545
5
18

Making select and delete queries as single statement

6 Answers6