UPDATE query with Select from two databases

Posted on 2009-04-18
Last Modified: 2012-05-06
I have to update a column in one database based on a select query that is between two databases checking for a null. This is what I have:

UPDATE db1.dbo.products  
SET active = '0'
FROM (SELECT db1.dbo.products.sku
      FROM db1.dbo.products
      LEFT JOIN db2.dbo.item ON db1.dbo.products.sku = db2.dbo.item.itemlookupcode
      WHERE db2.dbo.item.itemlookupcode IS NULL)

I am getting an invalid syntax error at ")"
I can't see what is wrong... please help.

Question by:fullbugg
    LVL 22

    Accepted Solution

    Try reformatting your query:
    UPDATE P  
    SET active = '0'
    FROM db1.dbo.products P
    LEFT JOIN db2.dbo.item 
    ON db1.dbo.products.sku = db2.dbo.item.itemlookupcode
    WHERE db2.dbo.item.itemlookupcode IS NULL;

    Open in new window

    LVL 12

    Assisted Solution

    replace       WHERE db2.dbo.item.itemlookupcode IS NULL)
          WHERE db2.dbo.item.itemlookupcode IS NULL) TableName

    Author Closing Comment

    8080_Diver ... Thanks so much... That did the trick

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video discusses moving either the default database or any database to a new volume.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now