mysql - 将选择和删除查询作为单个语句

Question

第一次查询

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

假设返回值为 730 和 735。

下一个查询

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

我希望将这两个查询合并为一个语句。如何做呢。请帮忙

我在下面试过这个。它不起作用。

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
                        );

Answer 1

在我看来，在一个查询中执行 delete-and-select 时，我们必须使用 IF-EXISTS 子句，因为如果 select 返回 null，它会抛出异常，所以试试这个:

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