SELECT within the UPDATE

Asked

Viewed 5,376 times

3

I have the following database:

    cd_telefone|cd_cliente  |nr_ddd |nr_telefone
    1          | 30         | 11    |2562-2791
    2          | 30         | 11    |2562-2791
    3          | 31         | 13    |8888-8888
    4          | 30         | 11    |5555-5555
    5          | 30         | 13    |9623-54002
    6          | 30         | 11    |1111-2525

My intention is to pass the value of ddd and phone, and return cd_phone, and from cd_phone, update the existing number.

My Code is so, not error, but does not work:

UPDATE tb_telefones SET nr_ddd='" + dddNovo + "', nr_telefone='" + telNovo+ "'
WHERE cd_telefone = (
    SELECT cd_telefone FROM tb_telefones 
    WHERE nr_ddd='" + dddAntigo + "' AND nr_telefone='" + telAntigo + "'
)
  • What database are you using? SQL Server / Oracle?

  • In fact it is access.

4 answers

5


As an explanation of the comment in my reply above:

the select you are using is :

    UPDATE tb_telefones SET nr_ddd='" + dddNovo + "', nr_telefone='" + telNovo+ "' 
    WHERE cd_telefone = (SELECT cd_telefone
                         FROM tb_telefones
                         WHERE nr_ddd='" + dddAntigo + "'
                           AND nr_telefone='" + telAntigo + "'
    )

try to use this way :

    UPDATE tb_telefones SET nr_ddd='" + dddNovo + "', nr_telefone='" + telNovo+ "'
    WHERE cd_telefone =(SELECT top 1 cd_telefone
                        FROM tb_telefones
                        WHERE nr_ddd='" + dddAntigo + "'
                          AND nr_telefone='" + telAntigo + "'
    )

so in select you take only the first value returned by select

  • Ta giving this error: Syntax error in query expression 'cd_phone =(SELECT top1 cd_phone FROM tb_phones WHERE nr_ddd='11' AND nr_phone='11112525')'

  • sorry the TOP 1 is separate

  • Fixed, it does not error, but still does not update the database.

  • The two Codes were right, inserting the attribute that was incorrect. Thank you for the force All

4

For it to work you would have to make sure that the result of your select is only of a value, you can do this by ultilizing for example :

SELECT top1 cd_telefone FROM tb_telefones WHERE nr_ddd='" + dddAntigo + "' AND nr_telefone='" + telAntigo + "')
  • Sorry I didn’t understand, I’m new in this area, what I must replace to return only one value?

  • 1

    there is a command inside the sql that is used in the "SELECT" which is the "TOP 1" and serves to return only the first record of your select

  • This command I am using in C#, I introduced TOP 1, when I run not the error, but it does not update the tbm database.

  • o seu update é o seguinte: UPDATE tb_telefones SET nr_ddd='" + dddNovo + "', nr_telefone='" + telNovo+ "'
WHERE cd_telefone = (
 SELECT cd_telefone FROM tb_telefones 
 WHERE nr_ddd='" + dddAntigo + "' AND nr_telefone='" + telAntigo + "'
)

  • Correct, my UPDATE is like this. However it does not update in the bank.

2

I usually use a loop in this situation:

for dd in( (SELECT cd_phone FROM tb_telefones WHERE nr_ddd = '" + dddAntigo + "' AND nr_telefone = '" + telAntigo + "')loop

UPDATE tb_phones SET nr_ddd = '" + dddNovo + "', nr_telefone = '" + telNovo+ "' WHERE cd_phone = dd.cd_phone;

end loop;

  • Don’t know much of the sql syntax, how would I put in my code? the complete code, no line break!

2

I’m sorry I hadn’t read your question properly.

I believe in your case just change the = for in

UPDATE tb_telefones SET nr_ddd='" + dddNovo + "', nr_telefone='" + telNovo+ "' WHERE cd_phone in ( SELECT cd_phone FROM tb_phones WHERE nr_ddd='" + dddAntigo + "' AND nr_telefone='" + telAntigo + "' )

Where will you include this code.

  • I’m using the code in c#. I put the code you gave me, not the error however, tbm does not update the database.

Browser other questions tagged

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