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.
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>
;
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;