Update if the value does not exist

Question

Update if the value does not exist

Navigation

#1 by (2 votes)

1

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

Banking Table

 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.

Account Table

 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 upgrade to 001 "of the NRBANCO "Account" If the current value does not exist in the "Banks" / em> .

sql sql-server

asked by anonymous 22.04.2014 / 16:17

1 answer

CakePHP does not show special characters present in the contents of the database How to map a http request parameter to an enum in java?

score 2 · Accepted Answer

This should do what you want:

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

As I can not test, first run the following SELECT to confirm that it returns all accounts in the situation that you have defined.

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