SQL Update with a Join Syntax

From: Dave <nospam_at_nospam.com>
Date: Fri, 06 Aug 2004 19:43:38 GMT
Message-ID: <KbRQc.89673$Np3.4331738_at_ursa-nb00s0.nbnet.nb.ca>

I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle.

I need to update a field in the NAMEINFO table for a particular surname in a particular town.

I can select the records fine with this syntax (testing in Oracle SQL* Plus)

SELECT NAMEINFO.LASTNAME, NAMEINFO.FIRSTNAME, NAMEINFO.MIDDLENAME, NAMEINFO.GENDER, ADDRESSINFO.REGION FROM NAMEINFO, ADDRESSINFO WHERE ADDRESSINFO.CIVICID =NAMEINFO.CIVICID (+) AND 'Smith'=NAMEINFO.LASTNAME AND 'Moncton'=ADDRESSINFO.TOWN;

However, I tried to update the names and failed. Here is my syntax: UPDATE NAMEINFO SET NAMEINFO.FLAG= 'OK' WHERE ADDRESSINFO.CIVICID =NAMEINFO.CIVICID (+) AND (('Smith'=NAMEINFO.LASTNAME) AND ('Moncton'=ADDRESSINFO.TOWN));

Is there anyway to update the FLAG field with using a Where clause using bits of 2 tables?
I can do it in Access, using the GUI, but the syntax is different between the 2 databases...

Help! Received on Fri Aug 06 2004 - 21:43:38 CEST