Geek Answers Handbook

Sql update table set - identifier with several parts cannot be associated

I have 2 tables:

  • Table 1 = station names (in pairs)
  • Table2 = has information coordination (longitude and latitude among other things)

Example Table1 :

StationID1 StationID2 Name1 Name2 Lattitude1 Longitude1 Lattitude2 Longitude2 Distance ------------------------------------------------------------------------------------------------ 93353477 52452 FOO BAR NULL NULL NULL NULL NULL 93353527 52452 HENRY BENNY NULL NULL NULL NULL NULL 93353551 52452 GALE SAM NULL NULL NULL NULL NULL

Example Table2 :

 IDInfo Name Lattitude Longitude ------------------------------------------- 93353477 BAR 37.929654 -87.029622

I want to update this table with the coordinate information that is in tableA . I tried to do the following according to SQL Server 2005: multi-part identifier ... could not be bound

 update table1 set t1.[Lattitude1] = t2.[Lattitude] from table1 t1 left join table2 t2 on (t1.StationID1 = t2.IDInfo)

The following error message appears:

Msg 4104, Level 16, State 1, Line 1
The multipart identifier "t1.Lattitude1" cannot be associated.

However, if I do the following, this works, and I can save it in another table.

 SELECT t1.[StationID1] ,t1.[StationID2] ,t1.[Name1] ,t1.[Name2] ,t2.[Lattitude] AS [Lattitude1] ,t2.[Longitude] AS [Longitude1] ,t3.[Lattitude] AS [Lattitude2] ,t3.[Longitude] AS [Longitude2] from table1 t1 left join table2 t2 on (t1.StationID1 = t2.IDInfo) left join table2 t3 on (t1.StationID2 = t2.IDInfo)

I am very new to SQL, and it’s hard for me to understand why some things work and others not. Based on the link I wrote above, my initial request should have worked - no? Maybe I don’t think directly, since I spent many hours on this, and I finally got help from an employee (she suggested the approach that I mentioned above).

+6
source share
2 answers

I think you can change the UPDATE statement to reference the table alias in the UPDATE row.

 update t1 set t1.[Lattitude1] = t2.[Lattitude] from table1 t1 left join table2 t2 on (t1.StationID1 = t2.IDInfo)
+12
source

You need to modify the internal table and assign different aliases to columns that are similar. That should work.

 update table1 set [Lattitude1] = x.[lat] from ( SELECT IDInfo [id], Lattitude [lat] FROM table2 ) x WHERE StationID1 = x.[id]

In your particular case, there is no need to rename Lattitude to lat, but if you end up updating the table by yourself and forcing yourself to specify different column names, this will save you headaches along the way.

0
source

More articles:


All Articles