May 9, 2006 at 2:23 pm
I am trying to update a table, but just cant wrap my head around how to do it. There are 3 columns in this table, here are a few example rows:
Master_Job, Job_Number, Bonded
1, 1, 0
2, 2, 1
3, 3, 1
3, 3.1, 1
3, 3.2, 0
3, 3.3, 0
What I am trying to do is update the bonded value to be the same for all rows that have the same master_job number, and to use the bonded value where master_job = job_number. (ie, all of the 3 jobs would have a bonded = 1)
I was trying something along the lines of
UPDATE tmp
SET tmp.Bonded =
(SELECT p.Bonded
FROM tmp as p
WHERE p.master_job = p.Job_Number )
WHERE EXISTS
(SELECT p.bonded
FROM tmp as p
WHERE p.Master_job <> p.Job_Number )
but this just set all bonded values to 1.
I am probably missing something obvious here, so any help would be appreciated!
May 9, 2006 at 2:38 pm
Using EXISTS here is your mistake... EXISTS does not evalute individual rows, it just finds out if the following query in parenthesis returns true or not.
Use a self-join...
UPDATE t
SET t.Bonded = t2.Bonded
FROM tmp t
JOIN tmp t2
ON t.Master_Job = t2.Master_job
AND t.Job_Number t2.Job_Number
AND t2.Master_Job = t2.Job_Number
I think that should work...
May 9, 2006 at 2:44 pm
Ah, thanks for clearing that up (and it worked perfectly BTW)!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply