Statement update SQL

Question

Good morning. I have the following SQL running:

UPDATE pessoas SET implante = true FROM saida WHERE saida.prontuario = pessoas.prontuario
and saida.mes LIKE (date_part('MONTH', CURRENT_DATE) || '%')

On the table pessoas are all records. In the table saida are only the records previously included. Thus, in the table saida there are not all the pessoas. It is possible that when running SQL above, in addition to registering the field implante as true according to the instruction, also record as false the rest of the field records implante table pessoas?

Answer 1

To make a UPDATE in the column IMPLANT table PEOPLE, with the parole of MES table EXIT, you need to perform a UPDATE with parole IN:

UPDATE pessoas 
SET implante = true 
WHERE pessoas.prontuario IN (SELECT saida.prontuario
                             FROM saida 
                             WHERE saida.mes LIKE (date_part('MONTH', CURRENT_DATE) || '%'))

Command instruction:

Update the table of people stating that the implant is true, where the chart number is found within the conditional in.

Conditional in search the numbers of the medical records in the exit table that come within the conditional set in Where.

To accomplish the UPDATE with the FALSE those that are not in Where condition, use the NOT IN:

UPDATE pessoas 
SET implante = false
WHERE pessoas.prontuario NOT IN (SELECT saida.prontuario
                                 FROM saida 
                                 WHERE saida.mes LIKE (date_part('MONTH', CURRENT_DATE) || '%'))

Answer 2

Good morning Jones, to use the same instruction that does the two functions you need and as taught by Hector, just join the two codes passed by him, for example:

UPDATE pessoas 
SET implante = true 
WHERE pessoas.prontuario IN (SELECT saida.prontuario
                         FROM saida 
                         WHERE saida.mes LIKE (date_part('MONTH', CURRENT_DATE) || 
'%'))

IF(@@ERROR = 0)
BEGIN

UPDATE pessoas 
SET implante = false 
WHERE pessoas.prontuario NOT IN (SELECT saida.prontuario
                         FROM saida 
                         WHERE saida.mes LIKE (date_part('MONTH', CURRENT_DATE) || 
'%'))

END

Explanation of the above code: I run update(true) for all people in the output table and if there is no error in this execution, I run update (false) for all people who are not registered in the output table.