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.
UPDATE SALES_DATA_FAMILY_2007 A
SET A.POG_ID=B.POG_ID
FROM POG_HIERARCHY B
WHERE A.FAMILY_ID=B.FAMILY ;
One alternative I have tried is as below. Unfortunately it gives another error: ORA-01427: single-row subquery returns more than one row
UPDATE SALES_DATA_FAMILY_2007
SET POG_ID= (SELECT POG_HIERARCHY.POG_ID FROM POG_HIERARCHY
WHERE SALES_DATA_FAMILY_2007.FAMILY_ID=POG_HIERARCHY.FAMILY_ID)
WHERE EXISTS (SELECT POG_HIERARCHY.POG_ID FROM POG_HIERARCHY
WHERE SALES_DATA_FAMILY_2007.FAMILY_ID=POG_HIERARCHY.FAMILY_ID)
;
Please help!
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