Update with Where in two different database

Asked

Viewed 20 times

0

I want to update the supplier database, in the hydro table, where the cod_barra field of the hydro table already has in the company database, in the products table. That is, I want to turn all bar Cod that are already registered in the company’s database into null so as not to give duplicate bar Cod when I register, however my syntax error code probably no from.

update fornecedor.hidro m set m.cod_barras = null from fornecedor.hidro m inner join empresa.produtos dbf on m.cod_barras = dbf.codigo_barras;

I already took the hydro table after from but it’s still giving error. I’m learning and I’m not sure how it can be done. I have the supplier database where have the hydro table which is where I want to update the bar Cod to null, but I will only update the bar Cod that already exist in the company database, in the products table.

Error: Error Code: 1064. You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near 'from cruzamento.mahle_filtros m Inner Join autogeral.produtos_dbf dbf on m.cod_b' at line 1

mysql sql-update

  • Wouldn’t they be different tables? You can edit the question to clarify the problem.

  • They are different tables and databases yes. I will edit.

  • What was the mistake? In principle Mysql allows to do operations on tables of different banks, since it prefixes each table name with the name of the bank (BANK.TABLE, BANK.TABELA.COLUMN, etc..)

  • I edited with the error, and also the from turns red and the following message: 'from' is not Valid at this position, expecting : EOF , ';'

1 answer

0

I figured out what the code would look like update where you need to search in two tables:

UPDATE TABELA a 
JOIN TABLEB b ON a.colA = b.colB 
SET a.columnToUpdate = [VALOR];

Browser other questions tagged mysql sql-update

You are not signed in. Login or sign up in order to post.