Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    3

    Unanswered: UPDATE & JOIN (with WHERE)

    Hi all,

    Can someone please help me with this update statement?
    Code:
    UPDATE Stats
    SET Stats.JobShownInResults = Stats.JobShownInResults + 1
    
    WHERE Jobs.JobID IN
    (
    SELECT Query that returns IDs from Search criteria
    )
    
    From Stats
    inner join Jobs
    ON Jobs.JobId = Stats.JobID
    I'm trying to increment a value in 'Stats' every time a job in 'Jobs' is returned in a search.

    Any help much appreciated,

    pete

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Try this:

    UPDATE Stats
    SET Stats.JobShownInResults = Stats.JobShownInResults + 1
    FROM Jobs
    INNER JOIN Stats on Stats.JobID = Jobs.JobId
    WHERE Jobs.JobID in
    (SELECT JobID
    FROM Stats s1)

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    JOINs are generally preferable to IN (SELECT...) clauses:

    UPDATE Stats
    SET Stats.JobShownInResults = Stats.JobShownInResults + 1
    From Stats
    inner join Jobs ON Jobs.JobId = Stats.JobID
    inner join ([your select query]) Subquery on Jobs.JobID = Subquery.JobID
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2004
    Posts
    3
    thanks for the help guys,

    Ive tried both of these bit Im getting '(0) rows affected'

    Ive tried the 'inner join ([your select query]) ' with the SELECT query being a valid query returning an existing ID but the main query is still not returning anything...

    I also tried the 'WHERE Jobs.JobID in (SELECT JobID FROM Stats s1)' with a valid IN query but that also returned none - That would make me assume that it wasn't the code you've given me.

    I just tried with the subQuery selecting ALL the jobs in the Job table. It said (14) rows affected but they are all still 0......

    any ideas?

    pete

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post your code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2004
    Posts
    3
    Sorry - My fault!!!!

    Not all the jobs have a corresponding row in the Stats table. It is created when the jobs are added (some test data was input before this) This is why the join wasnt working.

    Great code guys

    Thanks alot for the help,

    Pete

Posting Permissions

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