update table1 p set p.col1 in (select q.col2 from table2 where p.id=10)
value()函数好像只能用于object tables。见下例: SQL> CREATE type typ_test as object 2 (ID VARCHAR2(5), 3 COL1 VARCHAR2(10), 4 COL2 VARCHAR2(10)); 5 / Type created. SQL> create table table1 of typ_test; Table created. SQL> create table table2 of typ_test; Table created. SQL> insert into table2 values ('1','a1','b1'); 1 row created. SQL> insert into table1 values ('1',null,null); 1 row created. SQL> UPDATE table1 p SET VALUE(p) = 2 (SELECT VALUE(q) FROM table2 q WHERE p.id = q.id) 3 WHERE p.id = 1; 1 row updated. SQL> select value(p) from table1 p; VALUE(P)(ID, COL1, COL2) ------------------------------------------------------------ TYP_TEST('1', 'a1', 'b1')
UPDATE table1 p SET p.col1 = (SELECT q.vol1 FROM table2 q WHERE q.id=10)
谢谢楼上各位,我要更新的是table1中记录的所有字段,而不是某一个字段(col1),可我并不知道所有字段名称,只知道条件字段的名称和值,该怎么办? UPDATE table1 p SET VALUE(p) = SELECT VALUE(q) FROM table2 q WHERE p.id = q.id) WHERE p.id = 10 是我从oracle文档中找到的例子,可执行时报错:q是无效列名。why?
先把table2中内容全部插入到table1中: insert into table1 select * from table2; 然后对table1更新: update table1 set col1=a,col2=b; 如果想插入一定条件的值,可进行如下造作: 先把table2中内容全部插入到table1中: insert into table1 select * from table2 where p.id=10; 然后对table1更新: update table1 set col1=a,col2=b where p.id=10; 一定可以成功。
谢谢楼上的回贴。 我的目的是要将table2中col1=a and col2=b的记录的所有字段更新到table1中col1=a and col2=b的记录,而不仅仅是更新col1和col2两个字段。p.id=10并不是我的条件,只是例子中的条件。
你可以从user_tab_cols中获得某个表中所有字段的名称,然后用动态sql来执行。举例: DECLARE sql_str VARCHAR2 (400); set_str VARCHAR2 (200); CURSOR cur_cols IS SELECT column_name FROM user_tab_cols WHERE table_name = 'TABLE1'; BEGIN -- get column names FOR rec_cols IN cur_cols LOOP set_str := set_str || column_name || ', '; END LOOP; -- remove last , IF set_str IS NOT NULL THEN set_str := SUBSTR (set_str, 1, LENGTH (set_str) - 1); END IF; -- build sql_str sql_str := 'UPDATE TABLE1 SET (' || set_str || ') = (SELECT ' || set_str || ' FROM TABLE2 WHERE conditions ) ' || ' WHERE conditions'; -- run dynamic sql EXECUTE IMMEDIATE sql_str; END;
SQL> CREATE type typ_test as object
2 (ID VARCHAR2(5),
3 COL1 VARCHAR2(10),
4 COL2 VARCHAR2(10));
5 /
Type created.
SQL> create table table1 of typ_test;
Table created.
SQL> create table table2 of typ_test;
Table created.
SQL> insert into table2 values ('1','a1','b1');
1 row created.
SQL> insert into table1 values ('1',null,null);
1 row created.
SQL> UPDATE table1 p SET VALUE(p) =
2 (SELECT VALUE(q) FROM table2 q WHERE p.id = q.id)
3 WHERE p.id = 1;
1 row updated.
SQL> select value(p) from table1 p;
VALUE(P)(ID, COL1, COL2)
------------------------------------------------------------
TYP_TEST('1', 'a1', 'b1')
(SELECT q.vol1 FROM table2 q WHERE q.id=10)
UPDATE table1 p SET VALUE(p) =
SELECT VALUE(q) FROM table2 q WHERE p.id = q.id)
WHERE p.id = 10
是我从oracle文档中找到的例子,可执行时报错:q是无效列名。why?
可我的表并不是object table,只是结构一样,应该怎么办?谢谢
insert into table1 select * from table2;
然后对table1更新:
update table1 set col1=a,col2=b;
如果想插入一定条件的值,可进行如下造作:
先把table2中内容全部插入到table1中:
insert into table1 select * from table2 where p.id=10;
然后对table1更新:
update table1 set col1=a,col2=b where p.id=10;
一定可以成功。
我的目的是要将table2中col1=a and col2=b的记录的所有字段更新到table1中col1=a and col2=b的记录,而不仅仅是更新col1和col2两个字段。p.id=10并不是我的条件,只是例子中的条件。
DECLARE
sql_str VARCHAR2 (400);
set_str VARCHAR2 (200);
CURSOR cur_cols IS
SELECT column_name
FROM user_tab_cols
WHERE table_name = 'TABLE1';
BEGIN
-- get column names
FOR rec_cols IN cur_cols LOOP
set_str := set_str || column_name || ', ';
END LOOP;
-- remove last ,
IF set_str IS NOT NULL THEN
set_str := SUBSTR (set_str, 1, LENGTH (set_str) - 1);
END IF;
-- build sql_str
sql_str :=
'UPDATE TABLE1 SET ('
|| set_str
|| ') = (SELECT '
|| set_str
|| ' FROM TABLE2 WHERE conditions ) '
|| ' WHERE conditions';
-- run dynamic sql
EXECUTE IMMEDIATE sql_str;
END;