Re: SQL Update command

The syntax is:

UPDATE table
SET column = value
WHERE condition(so)

So you need to move your 'join' to the WHERE section, something like:

UPDATE hydrant
SET auth = 5
WHERE auth = 2
AND EXISTS (SELECT 1 FROM retic_main WHERE owner = 1 AND unique_id = owner_id)

Andy

Andrew <apomphre_at_sewl.com.au> wrote in message news:nb8F5.25802$O7.412195_at_ozemail.com.au...
> Im only a novice so please excuse this simple question:
> I am trying to update a column in a table (with related records in another
> table) and am having trouble with the syntax. I have setup the same query
 in
> MSAccess (using design view) and then viewed the sql syntax.
> It looked something like this: (ABCDBA is the owner of the table)
>
> UPDATE ABCDBA_HYDRANT INNER JOIN ABCDBA_RETICMAIN ON
 ABCDBA_HYDRANT.OWNER_ID
> = ABCDBA_RETICMAIN.UNIQUE_ID SET ABCDBA_HYDRANT.AUTH = 5
> WHERE (((ABCDBA_RETICMAIN.OWNER)=1) AND ((ABCDBA_HYDRANT.AUTH)=2));
>
> I changed it to this:
>
> update ABCDBA.HYDRANT JOIN ABCDBA_RETICMAIN ON ABCDBA.HYDRANT.OWNER_ID =
> ABCDBA.RETICMAIN.UNIQUE_ID SET ABCDBA_HYDRANT.AUTH = 5
> WHERE (((ABCDBA_RETICMAIN.OWNER)=1) AND ((ABCDBA_HYDRANT.AUTH)=2));
>
> Using SQL Plus Im having trouble trying to position the JOIN statement,
>
> Any Help Much Appreciated,
> Thanks
> Andrew
>
>
>
Received on Thu Oct 12 2000 - 02:40:02 CDT