Re: SQL Update with a Join Syntax
Date: Sat, 07 Aug 2004 14:15:27 GMT
Message-ID: <3u5Rc.35482$zc4.11861480_at_news4.srv.hcvlny.cv.net>
"Dave" <nospam_at_nospam.com> 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 Received on Sat Aug 07 2004 - 16:15:27 CEST