删除行然后执行选择查询

Question

I have to delete rows based on some condition and then i have select remaining rows using Select query, will it be possible in SQL ?.

both of this operation should goes with single query.

Please thought me, what is the primitive and best approach for this type of context.

Answer 1

Note: You can't do it all in one query, but you can do it all in one TRANSACTION if you are using a transactional store engine (like InnoDB). This might be what you want, but it's hard to tell only using the information you provided in your question.

Working on with TRANSACTION:

START TRANSACTION;

INSERT STMT;
DELETE STMT;
UPDATE STMT;

COMMIT;

You can delete and select based on the below query based on the subquery with the help if IN operators and WHERE.

IN Condition

The MySQL IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Note:

1. The MySQL IN condition will return the records where expression is value1, value2..., or value_n.
2. The MySQL IN condition is also called the MySQL IN operator.

DELETE from rooms WHERE initiating_user_id IN (SELECT user_id FROM users WHERE user_connected = 0)and target_user_id IN (SELECT user_id FROM users WHERE user_connected = 0)

Working on with Procedure:

You can create the procedure also for Doing this. Which will reduce the number of queries that you use in the Code.

CREATE PROCEDURE select_update_delete(a)
BEGIN

UPDATE table_one SET table_one.avalue=1 WHERE table_one.another=a;

DELETE FROM table_two WHERE table_two.avalue=a;

SELECT * 
FROM table_one
WHERE table_one.another=a;

END;

Hope so my explanation will be clear for understanding your doubts..

Thanks & Happy Coding :)