Update if value does not exist

Question

I have these two tables in the database (SQL Server)

Table Banks

 ID  CODIGO  NOME                                                     
 1   246     Banco ABC Brasil S.A. 
 2   075     Banco ABN AMRO S.A.   
 3   025     Banco Alfa S.A.       
 4   001     Banco do Brasil S.A.

Table Account

 NRCONTA AGENCIA NRBANCO NOMECOR                                    
 601     1       001     TESTE 1  
 1       1       001     TESTE 2  
 2       2       002     TESTE 3  
 601     1111    001     TESTE 4  
 7       1111    001     TESTE 5  
 27      1       027     TESTE 6  
 27      1       027     TESTE 6

I would like to update to "001" the value of the field "NRBANCO" table "Bill" if the current value does not exist in the table "Benches" field "CODE".

Answer 1

This should do what you intend:

UPDATE Conta SET NrBanco = '001'
 WHERE (SELECT COUNT(*) FROM Bancos AS B WHERE B.Codigo = Conta.NrBanco) = 0  

Since I can’t test, run the SELECT next to confirm if it returns all accounts in the situation you defined.

SELECT * FROM Conta AS C
 WHERE (SELECT COUNT(*) FROM Bancos AS B WHERE B.Codigo = C.NrBanco) = 0