SELECT within UPDATE

Question

SELECT within UPDATE

Navigation

#1 by (4 votes)
#2 by (4 votes)
#3 by (2 votes)
#4 by (2 votes)

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

sql c#

asked by anonymous 27.11.2014 / 19:08

4 answers

4

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

27.11.2014 / 19:20

2

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;

27.11.2014 / 19:51

2

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.

27.11.2014 / 20:06

Is it possible to emulate a single NFC ticket on my cell phone? Get most used words from a string

score 4 · Accepted Answer

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