SELECT within UPDATE

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 the ddd and phone, and return the cd_phone, and from the cd_phone, update the existing number.

My code is like this, not the error, but it 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 + "'
)

As an explanation of the comment in my answer 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 only get the first value returned by select

In order for it to work you would have to make sure that the result of your select is just one value, you can do this by using:

SELECT top1 cd_telefone FROM tb_telefones WHERE nr_ddd='" + dddAntigo + "' AND nr_telefone='" + telAntigo + "')

I usually use a loop in this situation:

for dd in ((SELECT cd_telephone
          FROM tb_telephones
         WHERE nr_ddd = ‘”+ dddAntigo +”‘
           AND nr_telephone = ‘”+ telAntigo +”‘) loop

UPDATE tb_telephones
   SET nr_ddd = ‘”+ dddNew +”‘, nr_phone = ‘”+ telNovo +”‘
 WHERE cd_telephone = dd.cd_telephone;

end loop;

I’m sorry I had not read your question with due attention.

I think in your case just changing the = to in

UPDATE tb_telefones SET nr_ddd = ‘”+ dddNovo +”‘, nr_telephone = ‘”+ telNovo +”‘
WHERE cd_telephone in (
    SELECT cd_telefone FROM tb_telephones
    WHERE nr_ddd = ‘”+ dddAntigo +”‘ AND nr_telephone = ‘”+ telAntigo +”‘
)

Where will you include this code.