Possible Duplicate:
How to UPDATE one column using another column in another table? SQL Error: ORA-00933: SQL command not properly ended可能重复:如何使用另一个表中的另一列更新一列? SQL错误:ORA-00933:SQL命令未正确结束
I have tried everything I can think of but couldn't solve this SQL error:
我已经尝试了我能想到的一切,但无法解决这个SQL错误:
SQL Error: ORA-00933: SQL command not properly ended
This is Oracle SQL.
这是Oracle SQL。
Have to say, Oracle SQL seems a lot more awkward and hard to use than the open-source version MySQL.... Many thanks.
不得不说,Oracle SQL似乎比开源版MySQL更尴尬和难以使用....非常感谢。
UPDATE SALES_DATA_FAMILY_2007 A
SET A.POG_ID=B.POG_ID
FROM POG_HIERARCHY B
WHERE A.FAMILY_ID=B.FAMILY
;
2 个解决方案
#1
0
Oracle doesn't support UPDATE FROM
syntax - or didn't last time I looked.
Oracle不支持UPDATE FROM语法 - 或者上次我没有查看。
Here is the BNF
:
这是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:
如果要设置A.POG_ID的所有值,可以使用以下语法:
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:
或者PL / SQL,只是为了更新sales_data_family_2007中适用的行:
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;
#2
0
If you want to update rows from a subquery with more than one record you can use the merge commenad:
如果要从具有多个记录的子查询更新行,可以使用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
#1
0
Oracle doesn't support UPDATE FROM
syntax - or didn't last time I looked.
Oracle不支持UPDATE FROM语法 - 或者上次我没有查看。
Here is the BNF
:
这是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:
如果要设置A.POG_ID的所有值,可以使用以下语法:
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:
或者PL / SQL,只是为了更新sales_data_family_2007中适用的行:
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;
#2
0
If you want to update rows from a subquery with more than one record you can use the merge commenad:
如果要从具有多个记录的子查询更新行,可以使用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