UPDATE & JOIN (and WHERE)

pete_m · June 30th, 2004, 06:08 AM

Hi,

Can anyone help with this UPDATE statement please...?

Code:

UPDATE Stats
SET JobShownInResults = JobShownInResults + 1

WHERE Jobs.JobID IN 
(
 [Query that returns list of IDs]
)
From Stats
inner join Jobs
ON Jobs.JobId = Stats.ID

Can anyone help me?
No matter what I do with the join I get an 'incorrect syntax' error.

cheers,

Pete

jemacc · June 30th, 2004, 06:34 AM

There are few things you should look at on your update and determine if that is what you want to do.
Look at "Jobs.JobId = Stats.ID and
"JobShownInResults + 1" are trying to Concatenate
the columns or trying to add one to JobShownInResults

Take a look below and it may help.

update stats
set jobshownInresults='jobshownInresults'
from stats join jobs
on (stats.statsID=jobs.jobID)

Lalit_Pratihari · September 28th, 2004, 05:19 AM

Hi,

You can use either of the two queries to update the field JobShownInResults.

Option 1:
----------
UPDATE Stats
SET JobShownInResults = JobShownInResults + 1
WHERE ID IN
(SELECT Jobs.JobID FROM Jobs INNER JOIN Stats ON Jobs.JobID = Stats.ID)

Option 2:
----------
UPDATE Stats
SET JobShownInResults = JobShownInResults + 1
From Stats inner join Jobs ON Jobs.JobId = Stats.ID
WHERE Jobs.JobID IN
(
[QUERY FOR LIST OF IDS ]
)

Hope this helps.

Lalit
Life Means More...;)