Update Query involving tables in two Databases

• Kathy Collins

  SSC Enthusiast

  Points: 141

  More actions

  March 24, 2005 at 6:36 am

  #163867

  I'm trying to update matching fields in matching tables that are located on the same SQL Server, but in different databases on that server.  It's a flag to tell me that data has been copied from one database to another.

  I've tried the simple:

  UPDATE Table1 SET saved = 1 WHERE Database1.dbo.Table1.Primarykey = Database2.dbo.Table2.Primarykey

  This didn't work, because it didn't understand what Database2 was.  (My syntax was incorrect?)

  I then created an update query in MSAccess that worked.  The tables in the databases were linked.  It looks like this.

  UPDATE Table1 INNER JOIN table2 ON Table1.PrimaryKey = Table2.PrimaryKey SET Table1.Saved = 1, Table2.Saved = 1;

  I'm trying to test this same procedure using Query Analyzer, but I don't seem to be getting the syntax right.  I am clueless about JOINs and what does what when where.

  I'd love some help.  Thanks,  Kathy

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  March 24, 2005 at 8:53 am

  #548777

  Sql Server has different syntax than MS-Access.

  Try:

  UPDATE Table1

  SET Saved = 1

  FROM Table1

  INNER JOIN table2 ON Table1.PrimaryKey = Table2.PrimaryKey;

  You can only update 1 table at a time, so you'll need to repeat for Table2.

• Kathy Collins

  SSC Enthusiast

  Points: 141

  More actions

  March 24, 2005 at 9:04 am

  #548786

  Thank you very much!  This works.   so simple!

  Kathy