Selecting and updating within stored procedure

Question

0.00/5 (No votes)

See more:

SQL

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 ;

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.

Posted 21-Apr-11 12:34pm

Maxdd 7

Updated 22-Apr-11 14:52pm

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Accepted Answer · 2011-04-21T18:54:00

Solution 1

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

SQL

DELETE FROM Relacoes WHERE idContacto1 = id;
SELECT idContacto2 FROM Relacoes WHERE idContacto1 = id INTO 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:

SQL

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?

Posted 21-Apr-11 18:54pm

Sandeep Mewara

Updated 22-Apr-11 17:40pm

v2

Comments

Maxdd 7 22-Apr-11 10:59am

No, idContacto2 where id is in another row :)

The problem is I'm not passing the variable the right way, I can't get the idc value to work.

Sandeep Mewara 22-Apr-11 11:26am

I am not sure what you are trying to convey but keeping those two statement together simply means you have removed the row with that id.

Maxdd 7 22-Apr-11 11:01am

Let's suppose that the DELETE statements are not here, how would be the right sintaxe to SELECT and then UPDATE?

Sandeep Mewara 22-Apr-11 11:27am

THats what, it's so all confusing and your question is not clear.

I already suggested how one can select and update using patameters. Did you see my answer completely?

Maxdd 7 22-Apr-11 20:51pm

I'm sorry, yes maybe it's confusing my question, much because the name of the fields I used.

I see your answer but I think does not help because if I do SELECT @idc..., I need to do SELECT idContacto2 (to extract from that specific field). I edited my question to be more clear, maybe you can take a look?

Sandeep Mewara 22-Apr-11 23:40pm

Updated my answer.

Maxdd 7 22-Apr-11 23:52pm

I didn't tested, but that doesn't matter: it makes all logic now. Thank you very much.