INNER JOIN in Update

Question

INNER JOIN in Update

Navigation

#1 by (2 votes)

1

I am migrating an Access query to SQL Server, the problem is that in Access it allows to perform INNER JOIN in the UPDATE

UPDATE (Aux_Nodos INNER JOIN Nodos_NULL_Titular ON Aux_Nodos.Necesidad = Nodos_NULL_Titular.Necesidad)
INNER JOIN Out_Arcos ON Aux_Nodos.Id_Nodo = Out_Arcos.Id_Nodo_Destino 
SET Out_Arcos.Id_Nodo_Destino = Nodos_NULL_Titular.Id_Nodo

At StackOverFlow in English, I saw that something had to be done, but I got an error

UPDATE dbo.Aux_Nodos SET Out_Arcos.Id_Nodo_Destino = Nodos_NULL_Titular.Id_Nodo
FROM Aux_Nodos 
INNER JOIN Nodos_NULL_Titular ON Aux_Nodos.Necesidad = Nodos_NULL_Titular.Necesidad
INNER JOIN Out_Arcos ON Aux_Nodos.Id_Nodo = Out_Arcos.Id_Nodo_Destino

The error that appears is "The multi-part identifier" dbo.Out_Arcos.Id_No_Destino "could not be bound.", it does not recognize it in SET, obviously that table and column exist.

sql sql-server ms-access

asked by Alejandro Ricotti 13.02.2017 в 16:38

source

1 answer

Error generating classes with xsd add Shortcode plugin custom

score 2 · Accepted Answer

According to your comments, what you need to do in SQL Server is:

UPDATE OA
SET OA.Id_Nodo_Destino = NN.Id_Nodo
FROM Out_Arcos OA
INNER JOIN Aux_Nodos AN
    ON OA.Id_Nodo_Destino = AN.Id_Nodo
INNER JOIN Nodos_NULL_Titular NN
    ON AN.Necesidad = NN.Necesidad;

This is the correct syntax for UPDATE with JOIN in SQL Server