SQL Update table from one database from other table from another database-CodePudding

I am trying to perform an update from a table in one database from another table in another database. One table has a number of lat/lon and I want to update the other one with those based on the matching address.

I have tried this:

UPDATE
    WENS_IMPORT.dbo.new_import 
SET
 WENS_IMPORT.dbo.new_import.lat = WENS.dbo.SUBSCRIPTION.lat,
 WENS_IMPORT.dbo.new_import.lon = WENS.dbo.SUBSCRIPTION.lon
FROM
 WENS.dbo.SUBSCRIPTION AS Table_A
 INNER JOIN WENS_IMPORT.dbo.new_import AS Table_B
    ON Table_A.streetAddress = Table_B.Address
WHERE
 Table_A.account_id = '388' AND Table_A.active = '1'

I thought this was the best route but I keep getting this error returned:

ERROR: The multi-part identifier "WENS.dbo.SUBSCRIPTION.lat" could not be bound. Error Code: 4104

Is this because it is seeing a number of records that match the address?

Any help would be greatly appreciated! Thanks so much!

CodePudding user response：

Thank you both for the responses. Dale was spot on and I went ahead and changed the alias' and it worked!

Here's the code for any other who has this issue:

 UPDATE
     Table_B
 SET
  Table_B.lat = Table_A.lat,
  Table_B.lon = Table_A.lon
 FROM
  WENS.dbo.SUBSCRIPTION AS Table_A
  INNER JOIN WENS_IMPORT.dbo.new_import AS Table_B
     ON Table_A.streetAddress = Table_B.Address
  WHERE
   Table_A.account_id = '388' AND Table_A.active = '1'

CodePudding user response：

You can not use for part name unless you use Linked Server . As you asked your both databases are attached to a same server . In this case use this pattern .<Owner/Schema>. . For example : WENS_IMPORT.dbo.new_import in fieldName it is not neseccery to Add table name Use: Set Table_B.lat=Table_A.lat

good luck