Post reply

Update Query involving tables in two Databases

  • 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

  • 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.

  • March 24, 2005 at 9:04 am

    #548786

    Thank you very much!  This works.   so simple!

    Kathy

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply