I try to update three tables in Oracle, which are totally different, but in all there is a field exactly the same "STATUS".
The issue is that I need these fields to be updated to "NEW".
This is why I have asked myself if it is possible to update multiple tables at the same time.
I thought of something like this, however it throws me wrong:
UPDATE SS_WERUECK, SS_BESTELLUNGR, SS_KOMMBESTR
SET STATUS = 'NEW';
I remain attentive in advance I appreciate it.
Try to perform a procedure:
Declare
cursor t is select table_name from user_tables where table_name in ('tabla1','tabla2'...); -- Colocas tus tablas entre comillas simples ('') y lo aloja en un cursor
begin
for t1 in t loop -- Recorremos nuestro cursor
execute immediate 'update '|| t1.table_name || ' set STATUS=''NEW'''|| ' where STATUS=''Status_a_cambiar'''; -- Ejecuta el update a nuestras tablas segun nuestro cursor
end loop;
commit; -- Guarda los cambios si no se tuvo error de ninguna actualización
exception when others then -- Captura si hay error
rollback; -- Devuelve todos los cambios realizados a las tablas
end;
This also applies if you want to delete data in tables. In case these are indexed, they will have to be eliminated in cascade considering the order of the first table as the data that must first be eliminated, until the last one that can be deleted once there is no place to index with that table. For this case remember to change "update" to "delete".
There is no UPDATE sentence that allows you to update the 3 tables of only one. But in reality, that should not matter.
If what you want is that all the changes are applied atomic, it's just a matter of the changes being made all within the same transaction:
update SS_WERUECK set status = 'NEW';
update SS_BESTELLUNGR set status = 'NEW';
update SS_KOMMBESTR set status = 'NEW';
commit;
And if you really want the 3 updates to be made within the same sentence for whatever reason, you can always do it with an anonymous PL / SQL block:
begin
update SS_WERUECK set status = 'NEW';
update SS_BESTELLUNGR set status = 'NEW';
update SS_KOMMBESTR set status = 'NEW';
end;
/
commit;
/