Post reply

Either Easy or Tough Join

  • 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

  • December 14, 2004 at 2:28 pm

    #533851

    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

    #533852

    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

     

  • December 14, 2004 at 3:01 pm

    #533857

    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