Post reply

UPDATE with subqueries in WHERE clause?

  • July 4, 2005 at 7:13 pm

    #186451

    Hi,

    Is this possible, and if so what is the correct syntax.

    I have one table called Contacts...

    pkContactID
    ContactName
    ContactAddress, etc.

    And one table called Projects

    pkProjectID
    fkClientLocationID
    fkContactID
    Project Name, etc.

    I want to pull the fkClientLocation field out of Projects and put it into Contacts.  So I added an empty field called fkClientLocationID to Contacts and I want to update it with an UPDATE query, something like

    UPDATE Contacts AS C
    Set fkClientLocationID =
    (SELECT fkClientLocationID FROM Projects AS P WHERE P.fkContactID=C.pkContactID)

    I know this is not the correct syntax.  Basically, how do I reference a field in the main query, from within the subquery?

    Thanks.

  • July 4, 2005 at 8:28 pm

    #570772

    Can't test the query from here, but it should be pretty close :

    UPDATE C

    Set C.fkClientLocationID = P.fkClientLocationID

    FROM dbo.Contacts inner join dbo.Project P on P.fkContactID=C.pkContactID

    Why exactly do you want to have the same information in 2 different tables?

  • July 4, 2005 at 8:49 pm

    #570774

    Hi Remi,

    I get a Syntax error (missing operator)

    Any ideas?

    Why is the same info in 2 tables?  Because it's what my client gave me, and I'm trying to get it normalized. 

    Thanks for your help.

    Aaron

  • July 4, 2005 at 9:48 pm

    #570775

    UPDATE C

    Set C.fkClientLocationID = P.fkClientLocationID

    FROM dbo.Contacts C inner join dbo.Project P on P.fkContactID=C.pkContactID

  • July 5, 2005 at 12:10 am

    #570786

    I know this is not the correct syntax. Basically, how do I reference a field in the main query, from within the subquery?

    UPDATE Contacts AS C

    Set fkClientLocationID =

    (SELECT fkClientLocationID FROM Projects AS P WHERE P.fkContactID=C.pkContactID)

    Simply remove the red items (the alias for the table to update) and the query is correct. Although Remi's query would win the performance race this time, this query is ANSI standard instead of the proprietary SQL Server syntax in the other one.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • July 5, 2005 at 2:04 am

    #570806

    Can this be adjusted to work with JET SQL, or just SQL Server?

    Aaron

  • July 5, 2005 at 5:55 am

    #570832

    No idea. Does not any of them work in Jet?

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • July 5, 2005 at 6:23 am

    #570835

    This syntaxe is just forbidden : UPDATE Contacts AS C

    You must use the as in the from clause. And yes my query will run much faster .

  • July 5, 2005 at 9:08 am

    #570887

    Yes, it can be adjusted for Jet, which is really uglier (is there such a word )

    UPDATE Contacts  inner join Project on Project.fkContactID = Contacts.pkContactID Set Contacts.fkClientLocationID = Project.fkClientLocationID;

     

     


    * Noel

  • July 5, 2005 at 9:13 am

    #570891

    Uglier is the right word here .

  • July 5, 2005 at 9:46 am

    #570904

    Thanks guys,

    I ran it in SQL Server and Imported the tables back into Access (I hate Jet SQL anyway).

    While it's currently in Access it's going to get centralized back into SQL Server at some point.

    The query worked beautifully.  Thank you.

    Aaron

  • July 5, 2005 at 9:52 am

    #570905

    HTH


    * Noel

  • July 5, 2005 at 9:56 am

    #570906

    I should send the link to sushila... proves I'm not the only one who HTHs guys to add to the posts count .

  • July 5, 2005 at 10:08 am

    #570912

    If you look at my posts I don't do this very often and I am not concerned at all with my post count  it was just a courtesy (so I thought :cool. But if it makes you happier I am not doing it any more

     

     


    * Noel

  • July 5, 2005 at 10:14 am

    #570920

    I don't mind... as long as you keep agreeing with my solutions .

Viewing 15 posts - 1 through 15 (of 17 total)

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