Update with JOIN with 2 tables together with Select

Asked

Viewed 67 times

-1

Hello, I’m with the following query to perform in a database but locked in a situation, below follows the query, the error message and then the explanation.

Excerpt from the Code

USE BDTESTE
GO

UPDATE NotaFiscalItens SET CODIGOPRODUTO = 
(SELECT B.CODIGO FROM EstoqueMercadorias as B
WHERE 
         CAST(B.REFERENCIACLIENTE as INTEGER) = CAST(CODIGOPRODUTO as INTEGER) 
AND B.EMPRESA = 1) 
WHERE 
    EMPRESA = 22 
AND SERIE = 2 
AND OPERACAO = 133 
AND DATA BETWEEN convert(smalldatetime,'01/06/2020',103) AND convert(smalldatetime,'30/06/2020',103)
GO

Error

Message 207, Level 16, Status 1, Line 13
Invalid DATE column name.
Message 207, Level 16, Status 1, Line 13
Invalid DATE column name.

Explanation

In the second WHERE where it reads: "AND DATA BETWEEN convert(smalldatetime,'01/06/2020',103) AND convert(smalldatetime,'30/06/2020',103)" the field DATE it comes from another table called NOTAFISCAL, and I’ve tried everything that is way researched on the internet, fit a JOIN with that table NOTAFISCAL in that query for me to be able to respect this condition of the initial and final period of the issuance of the notes to be updated the commodity code of the issued notes because they come from an integration with other software and I need to make this update which is practically a FROM/TO, with this the stock balance will be correct.

I’m grateful if someone can help me.

sql select join sql-update

  • Follow this example in English: [link] (https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server) . the objective will be to make the Join between the table invoice and invoice items

1 answer

0


There are several ways to achieve a UPDATE with INNER JOIN, the easiest and error-avoidance method is to perform a SELECT as if it were a crossing of tables and bring the value Ancient and the New and then give a SET with the nickname of the value New and Ancient, as the example below:

UPDATE 
     (SELECT tb1.valor_antigo AS OLD, tb2.valor_novo AS NEW
      FROM tabela_1 as tb1
      INNER JOIN tabela_2 as tb2
        ON tb1.cd_tabela = tb2.cd_tabela
      WHERE tb1.valor >= 100
      AND tb1.valor <= 200) up
SET up.OLD = up.NEW;

As you can see I used the WHERE, INNER JOIN and other commands of SELECT and then carry out the UPDATE.

Browser other questions tagged sql select join sql-update

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