UPDATE in multiple tables with the same column?

Asked

Viewed 2,141 times

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 certain data, the syntax below will work? 

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

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

mysql sql-update
  • 2

    If this information cannot be "guessed", it is not suitable. Anyway, it does it in 10 tables. It even has how to create a script to try to "automate" this, but it will take more work to do on hand with C V. Actually if you don’t have a clear pattern of change, you won’t even get any results.

  • Before I used random sequences of 9 numbers to create the user ID, but then I read a little about UUID and it seemed safer to use a more complex ID and more difficult to be guessed mentally, so I want to update

2 answers

1

This select will create an update set for all tables that fit the Where condition. Then you copy the result that will be several update, glue in Workspace and wheel.

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

  • This response was flagged as low quality due to size or content. Please, if possible and if this is a solution to the problem, explain better how you solved it. The code may be trivial to you, but to other users it may not be.

0

You can do a Join Inner in your update so update multiple table 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 only you apply the correct logic to your scenario. Open a transaction and take the test and if you need to make the transaction effective

  • Could make an example by adding more tables if possible?

  • Pronto @Leoletto, this will work for example with three tables.

Browser other questions tagged mysql sql-update

You are not signed in. Login or sign up in order to post.