在存储过程中选择和更新 [英] Selecting and updating within stored procedure

问题描述

DROP PROCEDURE IF EXISTS removerContacto;
DELIMITER |
CREATE PROCEDURE removerContacto (id INT)
BEGIN
DECLARE idc INT DEFAULT 0;
SELECT idContacto2 FROM Relacoes WHERE something = id INTO idc; 
UPDATE Contactos SET n=n-1 WHERE ctg = idc; -- need that idc be the result from the SELECT clause
END |
DELIMITER ;

我需要的是，在UPDATE中我需要填充WHERE子句ctg = idc，并且我希望idc是SELECT的结果值.

What I need is, in the UPDATE I need to fullfill that WHERE clause ctg = idc, and I want idc be the result value from the SELECT.

推荐答案

请注意，这两行之后会发生什么:

Did you notice that what will happen after these two lines:

DELETE FROM Relacoes WHERE idContacto1 = id;
SELECT idContacto2 FROM Relacoes WHERE idContacto1 = id INTO idc;

idc将始终由您设置默认值零.这是因为您已经删除了与其相关的记录.

纠正逻辑后，可以根据需要使用参数传递值.
宣告@idc Int
使用SELECT @idc ...
使用，更新... WHERE @idc = ...

更新:
我仍然不确定是什么让我感到困惑.试试:

idc will have always the default value of zero set by you. This is as you have already deleted the record related to it.

Once you correct your logic, you can use parameters to pass on the values if needed.
DECLARE @idc Int
Use, SELECT @idc...
Use, UPDATE... WHERE @idc=...

UPDATE:
I am still not sure what was so confusing. Try:

BEGIN
DECLARE @idc INT
SELECT @idc=idContacto2 FROM Relacoes WHERE something = id 
UPDATE Contactos SET n=n-1 WHERE ctg = @idc;
END

上面的东西谁不做?

Whould not the above thing do?

这篇关于在存储过程中选择和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！