How could I do a self-checking query in SQL, ie I would like to query such a value and then delete it in only one query. EX:
select * from usuarios where nome=lucas
that is, to get the result of searching and deleting.
I know I would then be able to delete using:
DELETE FROM usuarios WHERE nome = lucas
But this is not what I want, I was wondering if in just one query it is possible to return the value and then delete then.
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 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 stored procedures are so you can understand it, even if it is not difficult, it requires learning to understand and give maintenance of this solution.
It takes two bank accesses, one to look for 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');