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 + "'
)
c# sql

  • 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 c# sql

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