December 14, 2004 at 2:20 pm
Basically I have 2 tables, I need to update a field in one if there is NO match to the other
Update TableA
Set TableA.Flag = 'Y'
WHERE
TableA.Key1 <> TableB.Key1 AND TableA.Key2 <> TableB.Key2 AND TableB.FLD = '0'
I don't like using the WHERE NOT IN option, which I think I have working.
And just can't seem to find the JOIN that does it.
KlK
December 14, 2004 at 2:28 pm
Update TableA
Set TableA.Flag = 'Y'
from
tableA
left join
tableB on tableA.key1 = tableB.key1 and tableA.key2=tableB.key2 and tableB.FLd ='0'
where tableB.key1 is null and tableB.key2 is null
December 14, 2004 at 2:34 pm
I'm not fully sure what you are intending with the FLD = '0' piece. Do you only want to update those rows if there is a row in TableB that matches the two TableA keys but has a non-zero value for FLD? If so, try something like this:
UPDATE TableA
SET TableA.Flag = 'Y'
WHERE NOT EXISTS (select * from TableB
where TableB.Key1 = TableA.Key1
and TableB.Key2 = TableA.Key2
and TableB.FLD = '0'
 
That is, if there is a matching row on Key1 and Key2, but FLD is some other value, the update still occurs. If my assumption is incorrect, let me know.
This is my standard method. Another would be to use some outer joins, but I prefer the not exists . . . it's more intuitive to me.
Hope this helps,
Scott Thornburg
Volt Information Sciences
December 14, 2004 at 3:01 pm
Thanks Herb that did it.
I was close, I think I was just thinking too hard.
KlK
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply