Oracle update query question

[Kevin P Smith]

Probably not the ideal forum to post this, but I know you tend to get quick resulst here so I thought I'd give it a go.

I have a tabel where I need to update a column, but I can only find the ID of that record by search a second table.

Basically the pseudo code query is...

Update table1 status='Effective' where table ID = X, X = table 1 child_id where table 2 master_id = '123456'

This is being executed on an Oracle DB.

Here is my attempt at the query, but I get a

  ORA-00933: SQL command not properly ended  

error

UPDATE table1 a
SET a.status='Effective' 
INNER JOIN table2 b
ON a.id = b.child_id
WHERE b.master_id = '123456'

[Jeanne Boyarsky]

Keith,
This forum is fine - it's about SQL.

I remember having to do a nested query for the other table in updates in Oracle for it to work. It's been a while, but this looks familiar.

[Paul Campbell]

update table1
   SET a.status='Effective' 
 from table1 a
 inner join table2 b
    on b.child_id = a.id
 WHERE b.master_id = '123456' ;

or alternatively you can do this

update ( select status
           from table1 a
          inner join table2 b on a.id = b.child_id
          WHERE b.master_id = '123456') c
 set c.status='Effective' ;