Home / Questions / 49221
Accepted
Alejandro Ricotti
Asked: 2020-02-14 07:38:50 +0800 CST 2020-02-14 07:38:50 +0800 CST

INNER JOIN on Update

  • 772

I am migrating a query from Access to SQL Server, the problem is that Access allows 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

In StackOverFlow in English I saw that something like this had to be done, but it throws me 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_Nodo_Destino" could not be bound.", it does not recognize it in the SET, obviously that table and column exist.

sql

1 Answers

  1. Best 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 UPDATEwith JOINin SQL Server

    • 11