SQL Query autodelete

Asked

Viewed 74 times

1

As I could make a self-deletable query in SQL, I would like to query such value and then delete in just one query. EX:

select * from usuarios where nome=lucas

that is to receive the search and delete result.

I know I could erase it later using: 

DELETE FROM usuarios WHERE nome = lucas

But that’s not what I want, I wanted to know if in just one query it is possible to return the value and then delete.

mysql sql
  • 2

    For what purpose do you want to get everything in one query?? Doesn’t make much sense this.

  • Diego, I think he wants to show everyone who’s been excluded. So I think if that’s it, he wants to do something very practical in a single query. It can be interesting if there is a native function that allows such execution. Although it doesn’t make much sense.

  • @Danielomine if this is it, only comes to mind.

  • 1

    I miss this feature a lot. Unfortunately you will have to do two same operations. At least I do not know how to delete getting return. And it seems that the people did not understand the question.

  • Bacco5, You understood perfectly my question kkk Thank you, And Thank you also to all those who as much as they did not understand very well, Had the patience to come here to give an attention, Thanks guys, Well I had to choose to do in the traditional way that is 2 queries.

  • would be what you want? DELETE FROM usuarios WHERE nome like '%lucas%';

  • If you delete a record there is no way to bring it in this same query. I don’t quite understand its logic. But ideally you do the query first, did you find the data? OK, you can delete it now.

Show 2 more comments

3 answers

0

It is possible with the use of Stored Procedures:

/*Criar stored procedure*/
DELIMITER //
CREATE PROCEDURE auto_remove(IN nome VARCHAR(64))
BEGIN
  /*Criar uma tabela temporaraia para guardar o valor que você achou*/
  DROP TEMPORARY TABLE IF EXISTS temporaryUser;
  CREATE TEMPORARY TABLE IF NOT EXISTS temporaryUser AS (
    SELECT *
    FROM pessoas
    WHERE pessoas.nome LIKE nome
  );

  /*Deletar o valor da tabela original*/
  DELETE FROM pessoas WHERE pessoas.nome LIKE nome;

  /*retornar o valor*/
  SELECT  * from temporaryUser;
END //
DELIMITER;

It’s a quick fix, and it’s probably not the most elegant, but it worked. The problem with this solution is that despite reducing the number of accesses to the bank, you have to know what they are stored procedures In order to understand it, even though it is not difficult, it requires learning to understand and maintain this solution.

0

You can use the RETURNING command

DELETE FROM usuarios WHERE nome = lucas RETURNING *;

You can also use the name of the fields. Example:

DELETE FROM usuarios WHERE nome = lucas RETURNING id;

-2

It takes two bank accesses, one to search and one to delete. What you can do is a DELETE and SELECT in the same query. It would look something like this:

DELETE FROM table_name WHERE colunaA=(SELECT colunaA FROM table_name WHERE colunaA = 'nome');

  • Thanks friend for the help, but I decided to choose to do in the traditional way that is 2 queries. Note: Please guys let’s have a little more sensitivity, People come here with the intention of helping and always have some to negatively answer.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.