Post reply

Update Query - against 2 databases

  • October 12, 2006 at 6:57 pm

    #174866

    I have two databases in a development environment.  I need to update values in db1.table1 based upon values in db2.table1.

    I have done a similar update statement when the tables were within the same database.  In this case I would want to the same process but across two databases.

    Update People

    Set password=(Select results.password from results where (results.people_id=people.people_id) 

    Where Exists (Select results.people_id from results where (results.people_id=people.people_id)

    This updates all records where the people_id is identical in both people and results - but within the same database.

    Is there a similar syntax if the results table (or in reality both database will have same table names) is in the second database?

    Thanks for any input and help.

    dbuzz

     

     

  • October 13, 2006 at 6:33 am

    #665696

    You need to use the 3 part naming convention: - <database>.<owner>.<table>

    Something like the following should work:

    -- ANSI SQL

    UPDATE db1.dbo.People

    SET [password] =

     (SELECT P2.[password]

     FROM db2.dbo.People P2

     WHERE P2.people_id = db1.dbo.People.people_id)

    WHERE EXISTS

     (SELECT *

     FROM db2.dbo.People P3

     WHERE P3.people_id = db1.dbo.People.people_id)

    or

    -- T-SQL

    UPDATE P1

    SET [password] = P2.[password]

    FROM db1.dbo.People P1

     JOIN db2.dbo.People P2 ON P1.people_id = P2.people_id

     

  • October 13, 2006 at 7:58 am

    #665732

    Wow!

    Thanks!! 

    The good news - your first example is what I was trying to do and don't know where I was off making it not work so at least I was on the right track.

    However, your second example I believe is much more elegant and efficient and that is what I am using.

    Thank you for the help getting over the hump - MUCH appreciated.

  • October 13, 2006 at 8:22 am

    #665751

    You need to be careful with the second example as it is not portable to other db systems. (eg Oracle.)

     

  • October 13, 2006 at 10:26 am

    #665786

    Thanks for the heads up on portability, it's good to know.  Good/bad news for the future it appears I will be pretty much one track minded - with MS SQL. 

  • November 2, 2006 at 4:07 pm

    #669652

    A follow up related question - so I didn't want to start a new thread...

    Using the following

    Update P1

    Set [password]=P2.[password]

    from db1.dbo.people P1

    Join db2.dbo.People P2 on P1.people_id=P2.people_id

    This updates the single field in P1.  Is there an easy way to update all fields in P1 from P2 based upon the join, without listing out every field?

  • November 3, 2006 at 2:24 am

    #669714

    Nope, sorry. You're going to have to get used to typing.

    You can get management studio/enterprise manager to generate an update statement for you that at least has the full list of columns.

    In management studio, right click the table in object explorer, script table-> as update-> to clipboard

    Enterprise manageer is similar.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

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