From: bmarasca@optonline.net   
      
   "Dave"  wrote:   
      
   > Is there anyway to update the FLAG field with using a   
   > Where clause using bits of 2 tables?   
      
   Hi, Dave.  I think the syntax would be:   
      
   UPDATE NAMEINFO   
      
   SET NAMEINFO.FLAG = 'OK'   
      
   FROM NAMEINFO [LEFT OUTER(?)] JOIN ADDRESSINFO   
     ON NAMEINFO.CIVICID = ADDRESSINFO.CIVICID   
      
   WHERE NAMEINFO.LASTNAME = 'SMITH'   
     AND ADDRESSINFO.TOWN = 'MONTCON';   
      
   I can't reach an Oracle server from here, so I didn't test this, but it's   
   very standard SQL (not that that means anything to Oracle).  Essentially,   
   the problem is that you omitted the FROM clause.  You can't refer to a table   
   in a WHERE clause that isn't named in the FROM clause.   
      
   There are so many maddening things about Oracle, that I'm not guaranteeing   
   this will work.  It would work on any database grounded in relational   
   theory, but I looked forward to the chance to work with Oracle for years,   
   and I've been disappointed to discover that virtually everything about   
   Oracle is ad-hoc and based on no theory at all.  Every day, I waste hours   
   trying to figure out why entirely ordinary things just don't work in Oracle   
   ('Oh, you need to have some proprietary PRAGMA if you want to do that').   
      
   I hope I don't infuriate anyone for whom Oracle is a religion by saying   
   this.  I've spent my career interested in relational theory without respect   
   to any particular implementation.   
      
   A couple of comments.  First, JOINS should never be done in the WHERE   
   clause.  That's an archaic construction.  They should be done using the   
   appropriate JOIN syntax; this keeps the distinction between joins and   
   restricts clear.  I wasn't sure whether or why you were trying to do an   
   outer join, so I put that part of the syntax in brackets.  I can't see any   
   reason for an outer join in the query, but of course, I don't know what   
   you're trying to do.   
      
   Finally, restrict conditions should always be written as COLUMN = Value, not   
   the other way 'round.  It will work either way, but it isn't logical to   
   write Value = COLUMN.   
      
   I hope this was helpful.   
      
   Brian   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)