But I believe there's more to it and there should be more than just one column in the 2 tables and there should be a relation between the 2. Otherwise, what would those 2 tables be good for? Or is it that you are just playing without having any well-defined goal?
delete from table2; insert into table 2 select * from table1; commit;
That is admitting that code_x is the column by which the 2 tables are to relate. But there may be more than one column, which would demand more conditions in the where clause...
update table2 dest set idno=(select t.idno from table1 t where t.code_x=dest.code_x);
But keep in mind RDBMS is not designed to work like this.
SQL> select * from t1; NO ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected. SQL> set null null SQL> SQL> select * from t2; NO ---------- null null null null null null null null null null 10 rows selected. SQL> begin 2 for i in (select no from t1) 3 loop 4 update t2 set t2.no = i.no where t2.no is null and rownum <2; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> select * from t2; NO ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected.
Edited by: yxes2013 on 4.4.2013 4:37
SQL> update signature2 x set acrnumber=(select y.acrnumber from pictures2 y where x.rownum=y.rownum); 2 acrnumber=(select y.acrnumber from pictures2 y where x.rownum=y.rownum) * ERROR at line 2: ORA-01747: invalid user.table.column, table.column, or column specification SQL> merge into pictures2 a using (select acrnumber from signature2 )b on (a.rownum = b.rownum) when matched then update set a.acrnumber = b.acrnumber; 2 3 4 5 on (a.rownum = b.rownum) * ERROR at line 3: ORA-01747: invalid user.table.column, table.column, or column specification
yxes2013 wrote:Again SQL != SQLPlus. SQL (Structured Query Language) is a programming language and SQLPlus is a client tool like Toad. So Stop calling SQL as SQLPlus.
yes dear single sql only :)
because pl/sql is slower than sqlplus.
I am updating millions of rows for testing.When you say you don't have a joining condition it violates the basics of the set theory. So doing it in single SQL may not be possible.