Clever Geek Handbook

How to update all columns at once in ORACLE without highlighting the column name

How to update all columns at once without specifying the column name.

Let's say I have a table A 

ID  NAME  AGE
21  MATT  45

Table B 

ID NAME   AGE
21 SCOTT  24

What i expect

update table A 
   set A.columns=B.columns

I am basically trying to keep two tables in sync.

+3


source to share


2 answers


As far as I know, there is no way to update table column values ​​without specifying the column name. Your goal is for table A to have the same values ​​in table B, then you can delete all rows from table A and copy the data to table A.

delete from A;
insert into A select * from B;
commit;

If you have multiple reasons to use an operator update

and there are many columns, you can generate the statement update

using a dictionary.



select 'A.'||column_name||'=B.'||column_name||','
from all_tab_columns
where owner = 'your schema name'
  and table_name = 'A';

You run the query and copy the result and edit.

update A
set <paste the result of previous query here>
;
+2


source


You can do this in PLSQL:

for src in ( select * from B ) loop
  update A set ROW = src where A.id = src.id; 
end loop;


or to insert

for src in ( select * from B ) loop
  insert into A values src; 
end loop;
+1


source






More articles:


All Articles