Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40

    Unanswered: Update query problem

    Basically, i dont know which way to go. I want to update a field in one table by a "0" count of records in another table. The relation goes like this. A group has many or no meetings scheduled. If the group has no meeetings scheduled then i want to flag the record as inactive.

    I just cannot wrap my head around the solution.
    Keep in mind i am an electrician hehehe.

    Here is my query so far:

    UPDATE dbo.tblGroupsTEST
    SET Inactive = 1
    WHERE [SELECT dbo.tblGROUPSTEST.GroupName, COUNT(dbo.tblMEETINGS.MeetingID) AS Expr2
    FROM dbo.tblGROUPSTEST INNER JOIN dbo.tblMEETINGS ON dbo.tblGROUPSTEST.GroupID = dbo.tblMEETINGS.GroupID
    GROUP BY dbo.tblGROUPSTEST.GroupName
    HAVING (COUNT(dbo.tblMEETINGS.MeetingID) < 1)]

    I need a trained pair of eyes please. Thanks alot. Chris H.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40

    !!!!!

    I dont think that helped. Unless you were being sarcastic?
    Last edited by hildebran; 10-01-05 at 14:30.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I wasn't being sarcastic at all. Post an example as he explains in the blog. We should then be able to quickly assist you.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Click here to see a post that explains a way to present your question so you'll get much better quality answers.

    Flying mostly blind, but giving it a good shot I come up with:
    Code:
    UPDATE dbo.tblGroupsTEST
       SET Inactive = 1
       WHERE NOT EXISTS (SELECT *
          FROM dbo.tblMEETINGS
          WHERE  dbo.tblMEETINGS.GroupID = dbo.tblGroupsTEST.GroupID)
    -PatP

  6. #6
    Join Date
    Jan 2005
    Location
    Portland, Oregon
    Posts
    40

    Pat, that was perfect

    WHERE NOT EXISTS. I'll put that in my toolbox! I am not in the DB field and am able to figure out solutions on my own but this one i needed help. So thanks again.

    P.S.: I'l review the link you placed and figure out how to provide that info to you professionals for assistance.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •