How can I use the UPDATE command between two tables?

Asked

Viewed 9,765 times

0

I’m trying to update the field GRUPO table ESTOQUE value 1 (one) when the field NCM table EST_ADICIONAIS is equal to 21011000. Since, both tables have the field CODIGO with equal values. Use Firebird.

sql firebird

  • You have some sql you tried to do?

  • Another thing, if possible post the structure of the tables, because we need this to make sql correctly

  • Could you try this SQL and say the result (I have not tested)? UPDATE estoque as est INNER JOIN est_adicionais as eadd ON eadd.codigo = est.codigo SET est.grupo = 1 WHERE eadd.ncm = 21011000

  • Refael: Invalid token. Dynamic SQL Error. SQL error code = -104. Token Unknown - line 1, column 16. as.

  • I just updated him... could try again?

  • Try this sql to see if it works: update stock set stock.group = 1 Where stock Inner Join est_additional on (stock.code = est_additional.code) Where est_additional.ncm = 21011000

  • I tested after you updated, and returned me the error of my previous comment

  • Giovane, returned me the error : Invalid token. Dynamic SQL Error. SQL error code = -104. Token Unknown - line 1, column 52. Inner.

  • @Washington, test like this: update STOCK Inner Join EST_ADICIONAIS on EST_ADICIONAIS.CODE = STOCK.CODE Where EST_ADICIONAIS.NCM = '21011000' SET STOCK.GROUP = 1. It is the same way proposed by Rafael only changed the position of the WHERE.

  • @Washington, I think it’s best that you post the image of the relationship between the tables. We may end up misleading you in this way.

Show 5 more comments

2 answers

1

From what I understand, Washington, I believe this command will help you:

update estoque est 
set est.grupo = 1
where est.codigo in (
    select est_adi.codigo from est_adicionais est_adi where est_adi.ncm = '21011000'
)

1

Based on the vague idea I have of your tables I created a example to simulate your SQL statement.

I created the tables with the following structure:

  • STOCK (INT CODE, INT GROUP)
  • EST_ADICIONAIS (INT CODE, NCM varchar(8))

I made the required Inserts and, to update, as required, ran the following SQL:

UPDATE ESTOQUE INNER JOIN EST_ADICIONAIS ON ESTOQUE.CODIGO = EST_ADICIONAIS.CODIGO AND EST_ADICIONAIS.NCM = '21011000' SET ESTOQUE.GRUPO = 1

  • Geison Santos, ran and returned the error : Invalid token. Dynamic SQL Error. SQL error code = -104. Token Unknown - line 2, column 1. INNER.

Browser other questions tagged sql firebird

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