Oracle SQL Developer UPDATE error: SQL Error: ORA-00933: SQL command not properly ended

Question

Possible Duplicate:
How to UPDATE one column using another column in another table? SQL Error: ORA-00933: SQL command not properly ended

I have tried everything I can think of but couldn't solve this SQL error:

SQL Error: ORA-00933: SQL command not properly ended

This is Oracle SQL.

Have to say, Oracle SQL seems a lot more awkward and hard to use than the open-source version MySQL.... Many thanks.

UPDATE SALES_DATA_FAMILY_2007 A 
SET A.POG_ID=B.POG_ID 
FROM POG_HIERARCHY B
WHERE A.FAMILY_ID=B.FAMILY
;

Answer 1

Oracle doesn't support UPDATE FROM syntax - or didn't last time I looked.

Here is the BNF:

UPDATE [schema .] { table | view} [ alias ] SET column = { expr | subquery }  [, column = { expr | subquery }]...[WHERE condition] ;

(from: http://docs.oracle.com/html/A95915_01/sqcmd.htm)

If you want to set all values of A.POG_ID, you can use this syntax:

UPDATE SALES_DATA_FAMILY_2007 A 
SET A.POG_ID=(SELECT B.POG_ID 
          FROM POG_HIERARCHY B 
          WHERE A.FAMILY_ID=B.FAMILY);

Or PL/SQL, just to update the rows in sales_data_family_2007 which are applicable:

DECLARE
BEGIN
FOR sdf_row_to_update IN (
      SELECT A.ROW_ID sdf_rowid, B.POG_ID 
      FROM POG_HIERACHY B, SALES_DATA_FAMILY_2007 A
      WHERE B.FAMILY=A.FAMILY_ID) LOOP
   UPDATE sales_data_family_2007 
   SET pog_id=sdf_row_to_update.pog_id 
   WHERE rowid=sdf_row_to_update.sdf_rowid;
END LOOP;
END;

Answer 2

If you want to update rows from a subquery with more than one record you can use the merge commenad:

merge into SALES_DATA_FAMILY_2007 A
using (select POG_ID , FAMILY
                 FROM POG_HIERARCHY ) B
on (A.FAMILY_ID=B.FAMILY)
when matched then
update set A.POG_ID=B.POG_ID