I am trying to update data from another table/databases
Database 1 = Client, Table 1 = Customer$
Account Number, Zip and Phone
Database 2 = Site10 and Insurance
Need to update Site 10 with Zip from Database 1 which is the client
Need Help????
A:
You didn't provide the key fields to link the two tables together, so, assuming these databases both reside on the same server, and assuming that "AccountNumber" in the "Customer$" table in Database 1 is the key to the Site10 table in Database2:
UPDATE Database2.Site10
SET Zip=Database1.Customer$.Zip
INNER JOIN Database1.Customer$
ON Database1.Customer$.AccountNumber = Database2.Site10.AccountNumber
If "INNER" is not valid on your particular database, this should also work:
UPDATE Database2.Site10
SET Zip=Database1.Customer$.Zip
FROM Database2.Site10, Database1.Customer$
WHERE Database2.Site10.AccountNumber = Database1.Customer$.AccountNumber
Russ
2010-08-11 15:31:48
Both Databases reside on SQL2008 or Enterprise MGR...same server just different databases
Jazzy
2010-08-11 16:17:05
Key Field is Acct number
Jazzy
2010-08-11 16:17:46
tried the query above and received error message of Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'Inner'. I have checked and everything looks good.
Jazzy
2010-08-11 17:51:07
Inner might not be valid on your particular database--it is valid on SQL Server.Try replacing everything from the "INNER" to the end of the code with this instead:FROM Database2.Site10, Database1.Customer$WHERE Database2.Site10.AccountNumber = Database1.Customer$.AccountNumber
Russ
2010-08-12 12:49:36