Update between two tables with conditions

Question

I have a question in my work regarding locating and replacing a certain field, updating it with a value of a search.

I’ll give you an example:

I have this table that has LOC_NUM_SUB and note in the image above that 13 does not have MUN_NU, but the LOC_NU of another record already has. I need to search in this table LOC_NUM based on LOC_NUM_SUB and fill the LOC_NO_SUB MUN_NUB which is null.

Here is another example from the same table:

In this, note, that is several LOC_NU_SUB with MUN_NU null. And there is a LOC_NU with MUN_NU, accurate, again: fill the MUN_NU with the value found in LOC_NU.

I tried the following SQL statement:

UPDATE cepdb.log_localidade A2 SET MUN_NU = (SELECT A2.MUN_NU FROM cepdb.listacep A1 INNER JOIN cepdb.log_localidade A2 ON (A1.LOC_NUM_SUB = A2.LOC_NU) WHERE cepdb.listacep.LOC_NUM_SUB IS NOT NULL AND cepdb.log_localidade.LOC_NU = cepdb.listacep.LOC_NU)   Error Code: 1054. Unknown column 'cepdb.listacep.LOC_NUM_SUB';

But returns me error.

That I need to update another table that has LOC_NUM and MUN_NU only:

Where the MUN_NU is null, we need to put the correct code, relative to it.

Thanks in advance.

Answer 1

The error is because sgbd did not find the column in the subquery, because you renamed them to A1 and A2 and then used the origin name. Try this code :

UPDATE cepdb.log_localidade A2 
SET MUN_NU = (SELECT A2.MUN_NU 
FROM cepdb.listacep A1 
INNER JOIN cepdb.log_localidade A2 ON (A1.LOC_NUM_SUB = A2.LOC_NU) 
WHERE A1.LOC_NUM_SUB IS NOT NULL 
AND A2.LOC_NU = A1.LOC_NU) 

Maybe you need to put a Where to test this:

 Preciso procurar nesta tabela o LOC_NUM baseado no LOC_NUM_SUB e preencher o MUN_NU do LOC_NO_SUB que é null.