How to do update retrieving value from another table?

Question

Navigation

#1 by (2 votes)

1

I have a table in my DB, called contas . Where do I register all system accounts.

As soon as they are paid I make a update by changing their status. However, I send a single update for multiple accounts, as follows:

update contas
set status='0',
    valor_recebido='aqui preciso capturar o campo valor'
where id IN ($ids)

So far so good, but in the valor_recebido field I need to capture the value of the valor field of the same conta table.

Does anyone know if this is possible?

Do I make a update and at the same time assign the value of one field of the table, to another field?

mysql

asked by anonymous 04.07.2016 / 18:54

1 answer

Facebook, Google+ and Twitter sharing goals How and when to use "Shadows" and "Overrides"?

score 2 · Accepted Answer

If the field is of the same table and the same key, you only need to set the field to the value of the other column.

update contas
set status='0',
    valor_recebido= valor_dasuacoluna
where id IN ($ids)

Now if the field is of another different key (ID) you will have to do a JOIN or a sub select

   update contas
    set status='0',
        valor_recebido= (select valor_dasuacoluna from contas where  id = outroid)
    where id IN ($ids)