Either Easy or Tough Join

Question

Post reply

Either Easy or Tough Join

kknudson

SSCarpal Tunnel

Points: 4986
More actions
December 14, 2004 at 2:20 pm

#60618

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

herb walles Say Hey Kid Points: 692 More actions · Answer 1

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

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 2

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'

&nbsp

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

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 3

Thanks Herb that did it.

I was close, I think I was just thinking too hard.

KlK