UPDATE on multiple tables with the same column?

Question

UPDATE on multiple tables with the same column?

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

Let's imagine a scenario where I need to give a update in 10 different tables in the same column in all of them updating a given data, will the syntax below work?

UPDATE tab1, tab2, tab3... SET id_usuario = 'novo id' WHERE id_usuario = 'id antigo'

All tables have the same column id_usuario , this is a question merely out of curiosity.

mysql sql-update

asked by anonymous 12.01.2017 / 12:32

2 answers

Verify that the contents of the div loaded Create form of radiobuttons

score 1 · Answer 1

This select will create an update set for all tables that fit into the where condition. Then you copy the result that will be several update, paste in the workspace and runs.

 select 'update '||owner||'.'||table_name|| 
           ' set '||column_name||'= **VALOR_SET** '||
         ' where '||column_name||' in (**VALOR_ATUAL**);'
      from DBA_TAB_COLUMNS where column_name = '**COLUNA**';

score 0 · Answer 2

You can make an inner join in your update so update multiple tables at the same time.

UPDATE TABELA1 
SET 
    A.Campo = 'Valor',
    B.Campo = 'Valor',
    C.Campo = 'Valor'
FROM TABELA1 A
INNER JOIN TABELA2 B on B.Campo = A.Campo
INNER JOIN TABELA3 C on C.Campo = B.Campo

Then you apply the right logic to your scenario. Open a transaction and take the test and if you need it, make the transaction